Re: Re: [COMMITTERS] pgsql: Make the pg_stat_activity view call a SRF

On Tue, Aug 19, 2008 at 10:03:04PM -0400, Robert Treat wrote:
> On Monday 18 August 2008 10:53:51 Bruce Momjian wrote:
> > Tom Lane wrote:
> > > >> Thanks, and while I approve of that TODO, that's not actually the one
> > > >> I was talking about in the email. The one I was talking about was
> > > >> "change builtin set-returning functions to use OUT parameters so you
> > > >> can query them without knowing the result format" or something like
> > > >> that.
> > > >>
> > > >> So, please keep the one you added, but add this one as well.
> > > >
> > > > Uh, I need more details on this. Can you give an example?
> > >
> > > Good:
> > >
> > > regression=# select * from pg_get_keywords();
> > > word | catcode | catdesc
> > > -------------------+---------+-----------------------
> > > abort | U | Unreserved
> > > absolute | U | Unreserved
> > > access | U | Unreserved
> > > ...
> > >
> > > Not so good:
> > >
> > > regression=# select * from pg_show_all_settings();
> > > ERROR: a column definition list is required for functions returning
> > > "record"
> > >
> > > There's no longer any very good reason for built-in SRFs to not define
> > > their own output record type.
> >
> > TODO updated:
> >
> > * Fix all set-returning system functions so they support a wildcard
> > target list
> >
> > SELECT * FROM pg_get_keywords() works but SELECT * FROM
> > pg_show_all_settings() does not.
> >
>
> If this isn't critical, and no one is working on it yet, I can see about
> whittling away at it for 8.4.

Looks like there are just 5 of these:

SELECT n.nspname as "Schema",
p.proname as "Name",
pg_catalog.pg_get_function_result(p.oid) as "Result data type",
pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
AND p.proargtypes[0] IS DISTINCT FROM 'pg_catalog.cstring'::pg_catalog.regtype
AND NOT p.proisagg
AND pg_catalog.pg_get_function_result(p.oid) = 'SETOF record'
AND pg_catalog.pg_get_function_arguments(p.oid) !~ 'OUT'

Schema | Name | Result data type | Argument data types
------------+-----------------------+------------------+---------------------
pg_catalog | pg_cursor | SETOF record |
pg_catalog | pg_lock_status | SETOF record |
pg_catalog | pg_prepared_statement | SETOF record |
pg_catalog | pg_prepared_xact | SETOF record |
pg_catalog | pg_show_all_settings | SETOF record |
(5 rows)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate