Re: Returns setof record PG/PLSQL - Mailing list pgsql-general

John,
I changed your function like follows (Postgresql V8.03)

drop function generate_table_count ();
drop type rowcounts_t;
create TYPE rowcounts_t as (name TEXT, count int);
create or replace function generate_table_count () returns setof
rowcounts_t as
$$
declare
 tname record;
 c rowcounts_t;
 table text;
 s text;
begin
    for tname in select table_name from information_schema.tables
              where table_schema = 'public' loop
       s = 'select \''  || quote_ident(tname.table_name)::text ||  '\' as name,\
 count(*)  from ' || quote_ident(tname.table_name) ;
  -- following line for debug only
  -- raise notice 's = %',s;
      for c in execute s
      loop
      return next c;
      end loop;
    end loop;
    return;
end;
$$ language plpgsql;

select * from generate_table_count();


---------- Original Message -----------
From: John Wells <jb@sourceillustrated.com>
To: pgsql-general <pgsql-general@postgresql.org>
Sent: Sun, 14 Aug 2005 20:36:15 -0400
Subject: Re: [GENERAL] 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
------- End of Original Message -------