[GENERAL] Returns setof record PG/PLSQL

[John Wells]

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, and can't find an example in the
documentation.  I have it working by defining the function to return
setof text and then do return next as:

--
return next table || '' '' || count.count;
--

However, I really want each result (table name and count) to have it's
own column.

Can someone help me out or point me in the direction of documentation
that will show an example?  It shouldn't be this hard, it seems.

Thanks, as always, for your help,
John


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Returns setof record PG/PLSQL

[John Wells]

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 strugglingany insight you might have is very much appreciated.
Thanks,
John



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Returns setof record PG/PLSQL

[Tom Lane]

John Wells [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Returns setof record PG/PLSQL

[Jim Buttafuoco]

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 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 strugglingany 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

Re: [GENERAL] Returns setof record PG/PLSQL

[John Wells]

On Sun, 2005-08-14 at 20:53 -0400, Tom Lane wrote:
 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();

Sweet mercy...success.  Thanks...my forehead was a bit bloody from all
the head bashing going on over here... 

I have much to learn about pl/pgsql programming!

Thanks again!
John


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match