Database Reference
In-Depth Information
expected type text in column 1.
CONTEXT: PL/pgSQL function run_a_query(text)
line 6 at RETURN NEXT
Changing things a little more, we finally arrive at something that works.
hannu=# select * from run_a_query('select
usename::text,usesysid::int from pg_user') as
("user" text, uid int);
-[ RECORD 1 ]--
user | postgres
uid | 10
-[ RECORD 2 ]--
user | hannu
uid | 17573
What do we learn from this? PostgreSQL will let you return an arbitrary record from a
function, but it is very particular in how it does it. When you call the function, you will
need to be very deliberate about things, especially data types. PostgreSQL will use
default casts to convert data to different data types if it knows enough information.
But in a function such as this, much of that information is not known.
Returning SETOF ANY
There is another way to define functions which can operate on and return incomplete
type definitions, the
ANY*
pseudo-types.
Let's define a function, which turns a simple one-dimensional PostgreSQL array of
any type into a set of rows with one element of the same type.
CREATE OR REPLACE FUNCTION array_to_rows(
array_in ANYARRAY )
RETURNS TABLE(row_out ANYELEMENT)
AS $$
BEGIN
FOR i IN 1.. array_upper(array_in,1) LOOP
