Re: Re: [COMMITTERS] pgsql: Make the pg_stat_activity view call a SRF - Mailing list pgsql-hackers

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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

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