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 <[EMAIL PROTECTED]>
To: pgsql-general <[email protected]>
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 -------
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
