Re: Returns setof record PG/PLSQL

On Sun, 2005-08-14 at 18:56 -0400, John Wells wrote:
> In my quest to create a function that counts rows for all user tables in
> the database, I've written the following:

Based on another example I've found, I've tried the two following
variations (to no avail).  Getting "ERROR:  wrong record type supplied
in RETURN NEXT" on both counts:

-- Variation 1 ----------------------------------------------
drop function generate_table_count ();
create TYPE rowcounts_t as (name TEXT, count int);
create or replace function generate_table_count () returns setof
rowcounts_t 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)::text ||
        '''''' as name, count(*)  from '' ||
        quote_ident(tname.table_name) loop
            return next count;
        end loop;
    end loop;
    return;
end;
' language plpgsql;
-- Variation 2 ----------------------------------------------
drop function generate_table_count ();
create TYPE rowcounts_t as (name TEXT, count TEXT);
create or replace function generate_table_count () returns setof
rowcounts_t 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)::text ||
        '''''' as name, count(*)::text  from '' ||
        quote_ident(tname.table_name) loop
            return next count;
        end loop;
    end loop;
    return;
end;
' language plpgsql;
--

Still struggling....any insight you might have is very much appreciated.
Thanks,
John