CODEWITHSUNDEEP
functionpostgresqldynamic-sql
precose
precose

Reputation: 614

Return multiple results using dynamic sql (postgresql 8.2)

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$

Upvotes: 0

Views: 752

Answers (1)

Wolph
Wolph

Reputation: 80091

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.

Upvotes: 3

Related Questions