Database Reference
In-Depth Information
FOR retval IN EXECUTE query LOOP
RETURN NEXT retval;
END LOOP ;
END;
$$ LANGUAGE PLPGSQL;
This is a function that lets a user execute a query; quite useless as such, but it could
be used as the basis for more useful functions that, for example, let users run quer-
ies only at a certain time, or performs some checks on queries before running them.
If you try to simply run the query:
select * from run_a_query('select usename,
usesysid from pg_user');
You will get the following error:
ERROR: a column definition list is required
for functions returning "record"
LINE 1: select * from run_a_query('select
usename, usesysid from pg_...
^
To use this kind of function, you need to tell PostgreSQL what the return values will
be by adding a column definition list at call time in the following way:
select * from run_a_query('select
usename,usesysid from pg_user') as ("user"
text, uid int);
So will this work? No, you will get the following error:
ERROR: wrong record type supplied in RETURN
NEXT
DETAIL: Returned type name does not match
