pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

SETOF RECORD RETURN VALUE
2+ messages / 2 participants
[nested] [flat]

* SETOF RECORD RETURN VALUE
@ 2005-10-26 10:34 Christian Paul B. Cosinas <cpc@cybees.com>
  2005-10-26 10:58 ` Re: SETOF RECORD RETURN VALUE Sean Davis <sdavis2@mail.nih.gov>
  0 siblings, 1 reply; 2+ messages in thread

From: Christian Paul B. Cosinas @ 2005-10-26 10:34 UTC (permalink / raw)
  To: pgsql-sql

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"



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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: SETOF RECORD RETURN VALUE
  2005-10-26 10:34 SETOF RECORD RETURN VALUE Christian Paul B. Cosinas <cpc@cybees.com>
@ 2005-10-26 10:58 ` Sean Davis <sdavis2@mail.nih.gov>
  0 siblings, 0 replies; 2+ messages in thread

From: Sean Davis @ 2005-10-26 10:58 UTC (permalink / raw)
  To: Christian Paul B. Cosinas <cpc@cybees.com>; pgsql-sql

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_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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2005-10-26 10:58 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-26 10:34 SETOF RECORD RETURN VALUE Christian Paul B. Cosinas <cpc@cybees.com>
2005-10-26 10:58 ` Sean Davis <sdavis2@mail.nih.gov>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox