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

Search Discussions

  • John Wells at Aug 15, 2005 at 12:35 am

    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
  • Jim Buttafuoco at Aug 15, 2005 at 1:06 am
    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 -------
  • Tom Lane at Aug 15, 2005 at 12:53 am

    John Wells 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
  • John Wells at Aug 15, 2005 at 1:25 am

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedAug 14, '05 at 10:56p
activeAug 15, '05 at 1:25a
posts5
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase