Return multiple results using dynamic sql (postgresql 8.2)

Question

I want to loop through schemas and get a result set that looks like this:

Count
5
834
345
34
984

However, I can't get it to return anything using dynamic sql...I've tried everything but 8.2 is being a real pain. No matter what return type i put I keep getting this error:

ERROR: ERROR: RETURN cannot have a parameter in function returning
set

Here is my function:

CREATE OR REPLACE FUNCTION dwh.adam_test4()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
   myschema text;
   rec RECORD;  
BEGIN 

FOR myschema IN select distinct c.table_schema, d.p_id
from information_schema.tables t inner join information_schema.columns c 
  on (t.table_schema = t.table_schema and t.table_name = c.table_name) 
  join dwh.sgmt_clients d on  c.table_schema  = lower(d.userid)
where c.table_name = 'fact_members' and c.column_name = 'debit_card'
  and t.table_schema NOT LIKE 'pg_%'
  and t.table_schema NOT IN ('information_schema', 'ad_delivery', 'dwh', 'users', 'wand', 'ttd') 
order by table_schema

LOOP
    EXECUTE 'select count(ucic) from '|| myschema || '.' ||'fact_members where debit_card = ''yes''' into rec;
    RETURN rec;
END LOOP;
END
$function$

Wolph · Accepted Answer · 2013-10-29 16:09:53Z

3

Perhaps I'm misunderstanding the issue, but shouldn't you tell Postgres that you want to return a record when you are returning a record?

At the bottom you have:

RETURN rec;

While in the function definition you say:

RETURNS void

Besides that, you are returning 1 result (return ends the function) so it won't return anything beyond the first element anyhow.

I would guess you need something like this instead:

RETURNS SETOF record

And instead of RETURN rec you would need:

RETURN NEXT rec

Since you want to return an integer, not sure if you actually want or need a RECORD to return.

edited Oct 29, 2013 at 16:09

answered Oct 29, 2013 at 16:04

Wolph

80.4k12 gold badges142 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

precose Over a year ago

You are right. I tried a bunch of different RETURNS and couldn't get what I wanted. I made your changes and am now receiving the error ERROR: ERROR: a column definition list is required for functions returning "record"

Wolph Over a year ago

That is to be expected with a RECORD, it is a generic type without any definition as to what it contains. When selecting from it you need to tell Postgres what is contained within the record (an integer in this case). So expect something like this SELECT * FROM your_function() name_of_the_result(cnt integer)

precose Over a year ago

That worked. How would I return 2 types of columns with that function?

Wolph Over a year ago

In that case it would be something like name_of_the_result(cnt integer, something_else char)

Collectives™ on Stack Overflow

Return multiple results using dynamic sql (postgresql 8.2)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related