Thread: Re: [COMMITTERS] pgsql: Make the pg_stat_activity view call a SRF
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 butSELECT * FROM pg_show_all_settings() does not. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
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.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
Robert Treat <xzilla@users.sourceforge.net> writes:
> On Monday 18 August 2008 10:53:51 Bruce Momjian wrote:
>> TODO updated:
>> * Fix all set-returning system functions so they support a wildcard
>> target list
> If this isn't critical, and no one is working on it yet, I can see about
> whittling away at it for 8.4.
I think someone did already volunteer.
regards, tom lane
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
On Tue, Aug 19, 2008 at 10:56 PM, David Fetter <david@fetter.org> wrote: >> >> If this isn't critical, and no one is working on it yet, I can see about >> whittling away at it for 8.4. > i'm doing it... > Looks like there are just 5 of these: > > 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 | i have done pg_lock_status and pg_show_all_settings the other three will do tomorrow -- regards, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. (593) 87171157