Return multiple results using dynamic sql (postgresql 8.2)
- by precose
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.
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$
