From: | Sean Davis <sdavis2(at)mail(dot)nih(dot)gov> |
---|---|
To: | "Christian Paul B(dot) Cosinas" <cpc(at)cybees(dot)com>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: SETOF RECORD RETURN VALUE |
Date: | 2005-10-26 10:58:16 |
Message-ID: | BF84D988.11C6E%sdavis2@mail.nih.gov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 10/26/05 6:34 AM, "Christian Paul B. Cosinas" <cpc(at)cybees(dot)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_list')
>
> 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_list') 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/guides/SetReturningFunctions
Sean
From | Date | Subject | |
---|---|---|---|
Next Message | Mario Splivalo | 2005-10-26 12:38:42 | RETURNS SETOF primitive returns results in parentheses |
Previous Message | Christian Paul B. Cosinas | 2005-10-26 10:34:44 | SETOF RECORD RETURN VALUE |