Обсуждение: 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