Re: Returns setof record PG/PLSQL

John Wells <jb(at)sourceillustrated(dot)com> writes:
> In my quest to create a function that counts rows for all user tables in
> the database, I've written the following:
> --
> drop function generate_table_count ();
> create or replace function generate_table_count () returns setof record
> as '
> declare
> tname record;
> count record;
> table text;
> begin
> for tname in select table_name from information_schema.tables
> where table_schema = ''public'' loop
> for count in execute ''select '''''' ||
> quote_ident(tname.table_name) ||
> '''''' as name, count(*) from '' ||
> quote_ident(tname.table_name) loop
> table := count.name;
> return next;
> end loop;
> end loop;
> return;
> end;
> ' language plpgsql;
> --

> Problem is, I can't figure out what parameters to pass to "return next;"
> to make this return properly,

I think you really want to use a named rowtype for the result.
Something like

regression=# create type table_count_result as (table_name text, count bigint);
CREATE TYPE
regression=# create or replace function generate_table_count ()
regression-# returns setof table_count_result as $$
regression$# declare
regression$# tname record;
regression$# count table_count_result;
regression$# begin
regression$# for tname in select table_name from information_schema.tables
regression$# where table_schema = 'public' loop
regression$# for count in execute 'select ' || quote_literal(tname.table_name) || ', count(*) from ' || quote_ident(tname.table_name) loop
regression$# return next count;
regression$# end loop;
regression$# end loop;
regression$# return;
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# select * from generate_table_count();
...

If you use "setof record" then you have to declare the result type in
the calling query, which is a pain in the neck.

In 8.1 it'll be possible to avoid the named rowtype by using OUT
parameters, but for now, this is the best solution.

regards, tom lane