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
