PostgreSQL SQL - SETOF RECORD RETURN VALUE

Author

SETOF RECORD RETURN VALUE

Christian Paul B. Cosinas

2005-10-27, 8:08 am

Hi I am having some problem with function that returns SETOF RECORD

Here is my function:

CREATE OR REPLACE FUNCTION test_record(text)
RETURNS SETOF RECORD AS
$BODY$

DECLARE
p_table_name ALIAS FOR $1;
temp_rec RECORD;
v_query text;

BEGIN

v_query = 'SELECT * FROM ' || p_table_name; FOR temp_rec IN EXECUTE v_query
LOOP
RETURN NEXT temp_rec;
END LOOP;

RETURN ;

END;

$BODY$
LANGUAGE 'plpgsql' VOLATILE;

And here is how I execute the function:
select * from test_record('field_l
ist')

I have this error:

ERROR: a column definition list is required for functions returning
"record"

I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Sean Davis

2005-10-27, 8:08 am

On 10/26/05 6:34 AM, "Christian Paul B. Cosinas" <cpc@cybees.com> wrote:

> Hi I am having some problem with function that returns SETOF RECORD
>
> Here is my function:
>
> CREATE OR REPLACE FUNCTION test_record(text)
> RETURNS SETOF RECORD AS
> $BODY$
>
>
> DECLARE
> p_table_name ALIAS FOR $1;
> temp_rec RECORD;
> v_query text;
>
> BEGIN
>
> v_query = 'SELECT * FROM ' || p_table_name; FOR temp_rec IN EXECUTE v_query
> LOOP
> RETURN NEXT temp_rec;
> END LOOP;
>
> RETURN ;
>
> END;
>
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
>
> And here is how I execute the function:
> select * from test_record('field_l
ist')
>
> I have this error:
>
> ERROR: a column definition list is required for functions returning
> "record"

Since Postgres doesn't know what to expect from your function, you have to
tell it by giving the list of columns that are actually returned:

select * from test_record('field_l
ist') as s(a,b,c,d)

where a,b,c,d are the columns in your returned set. (ie., in your example,
if p_table_name has 5 columns, you would use "as s(a,b,c,d,e)", etc.).

See here for more detail:

http://techdocs.postgresql.org/guid...unction
s

Sean

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links