Returns setof record PG/PLSQL

Поиск
Список
Период
Сортировка
От John Wells
Тема Returns setof record PG/PLSQL
Дата
Msg-id 1124060196.21366.19.camel@localhost.localdomain
обсуждение исходный текст
Ответы Re: Returns setof record PG/PLSQL  (John Wells <jb@sourceillustrated.com>)
Re: Returns setof record PG/PLSQL  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: Kostas Karadamoglou
Дата:
Сообщение: Design problem About application related with cached rows
Следующее
От: Brent Wood
Дата:
Сообщение: Re: Removing -'s (header) before records in psql