Thread: How to get stored procedure args list from metadata tables ?
Hi: Looking for a table or view which contains the list of arguments that are p= assed to a stored procedure. Doesn't seem to be in pg_proc.prosrc or other= pg_proc columns. Thanks in Advance for any help.
Hello
2013/2/13 Gauthier, Dave <dave.gauthier@intel.com>:
> Hi:
>
>
>
> Looking for a table or view which contains the list of arguments that are
> passed to a stored procedure. Doesn't seem to be in pg_proc.prosrc or ot=
her
> pg_proc columns.
>
It is in pg_proc - argument's description is stored in combination of
fields: pronargs, pronargdefaults, proargtypes, proallargtypes,
proargmodes, proargnames, proargdefaults
system function pg_get_function_arguments can returns usual description
postgres=3D# select pg_get_function_arguments('check_form_rodne_cislo'::reg=
proc);
pg_get_function_arguments
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80
character varying, boolean
(1 row)
regards
Pavel Stehule
>
>
> Thanks in Advance for any help.
On 02/13/2013 06:13 AM, Gauthier, Dave wrote: > Hi: > > Looking for a table or view which contains the list of arguments that > are passed to a stored procedure. Doesn't seem to be in pg_proc.prosrc > or other pg_proc columns. > > Thanks in Advance for any help. > http://www.postgresql.org/docs/9.2/interactive/catalog-pg-proc.html proargtype, proallargtypes, proargmodes, proargnames, proargdefaults Example: production=# SELECT proargnames from pg_proc where proname ='close_tag_order'; proargnames -------------------------------------- {_order_no,not_rcv_ct,tag_change_ct} -- Adrian Klaver adrian.klaver@gmail.com
Dave Gauthier wrote:
> Looking for a table or view which contains the list of arguments that are=
passed to a stored
> procedure. Doesn't seem to be in pg_proc.prosrc or other pg_proc columns=
.
This information is in the following columns of pg_proc:
proargtypes, proallargtypes, proargmodes, proargnames
For a nice display, use
SELECT pg_get_function_identity_arguments('funcname'::regproc);
Yours,
Laurenz Albe
Excellent !
Thank You very much !
-----Original Message-----
From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at]=20
Sent: Wednesday, February 13, 2013 9:30 AM
To: Gauthier, Dave; pgsql-general@postgresql.org
Subject: RE: How to get stored procedure args list from metadata tables ?
Dave Gauthier wrote:
> Looking for a table or view which contains the list of arguments that=20
> are passed to a stored procedure. Doesn't seem to be in pg_proc.prosrc o=
r other pg_proc columns.
This information is in the following columns of pg_proc:
proargtypes, proallargtypes, proargmodes, proargnames
For a nice display, use
SELECT pg_get_function_identity_arguments('funcname'::regproc);
Yours,
Laurenz Albe
Actually, how do you discriminate between 2 different procedures that have = the same name but different number of args (using pg_get_function_arguments= )? The function bocks when the procedure name is not unique. -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@gmail.com]=20 Sent: Wednesday, February 13, 2013 9:26 AM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] How to get stored procedure args list from metadata = tables ? On 02/13/2013 06:13 AM, Gauthier, Dave wrote: > Hi: > > Looking for a table or view which contains the list of arguments that=20 > are passed to a stored procedure. Doesn't seem to be in=20 > pg_proc.prosrc or other pg_proc columns. > > Thanks in Advance for any help. > http://www.postgresql.org/docs/9.2/interactive/catalog-pg-proc.html proargtype, proallargtypes, proargmodes, proargnames, proargdefaults Example: production=3D# SELECT proargnames from pg_proc where proname =3D'close_tag_= order';=20 proargnames=20 =20 --------------------------------------=20 =20 {_order_no,not_rcv_ct,tag_change_ct} -- Adrian Klaver adrian.klaver@gmail.com
SXMgdGhlcmUgYW55dGhpbmcgbGlrZSB0aGlzIHNvbG4gZm9yIHY4LjMuNCA/DQpJIGtub3csIEkg a25vdywganVzdCB1cGdyYWRlLiAgQnV0IGl0cyBvdXQgb2YgbXkgaGFuZHMuDQoNCg0KLS0tLS1P cmlnaW5hbCBNZXNzYWdlLS0tLS0NCkZyb206IFBhdmVsIFN0ZWh1bGUgW21haWx0bzpwYXZlbC5z dGVodWxlQGdtYWlsLmNvbV0gDQpTZW50OiBXZWRuZXNkYXksIEZlYnJ1YXJ5IDEzLCAyMDEzIDk6 MjQgQU0NClRvOiBHYXV0aGllciwgRGF2ZQ0KQ2M6IHBnc3FsLWdlbmVyYWxAcG9zdGdyZXNxbC5v cmcNClN1YmplY3Q6IFJlOiBbR0VORVJBTF0gSG93IHRvIGdldCBzdG9yZWQgcHJvY2VkdXJlIGFy Z3MgbGlzdCBmcm9tIG1ldGFkYXRhIHRhYmxlcyA/DQoNCkhlbGxvDQoNCjIwMTMvMi8xMyBHYXV0 aGllciwgRGF2ZSA8ZGF2ZS5nYXV0aGllckBpbnRlbC5jb20+Og0KPiBIaToNCj4NCj4NCj4NCj4g TG9va2luZyBmb3IgYSB0YWJsZSBvciB2aWV3IHdoaWNoIGNvbnRhaW5zIHRoZSBsaXN0IG9mIGFy Z3VtZW50cyB0aGF0IA0KPiBhcmUgcGFzc2VkIHRvIGEgc3RvcmVkIHByb2NlZHVyZS4gIERvZXNu J3Qgc2VlbSB0byBiZSBpbiANCj4gcGdfcHJvYy5wcm9zcmMgb3Igb3RoZXIgcGdfcHJvYyBjb2x1 bW5zLg0KPg0KDQpJdCBpcyBpbiBwZ19wcm9jIC0gYXJndW1lbnQncyBkZXNjcmlwdGlvbiBpcyBz dG9yZWQgaW4gY29tYmluYXRpb24gb2YNCmZpZWxkczogcHJvbmFyZ3MsIHByb25hcmdkZWZhdWx0 cywgcHJvYXJndHlwZXMsIHByb2FsbGFyZ3R5cGVzLCBwcm9hcmdtb2RlcywgcHJvYXJnbmFtZXMs IHByb2FyZ2RlZmF1bHRzDQoNCnN5c3RlbSBmdW5jdGlvbiBwZ19nZXRfZnVuY3Rpb25fYXJndW1l bnRzIGNhbiByZXR1cm5zIHVzdWFsIGRlc2NyaXB0aW9uDQoNCg0KDQpwb3N0Z3Jlcz0jIHNlbGVj dCBwZ19nZXRfZnVuY3Rpb25fYXJndW1lbnRzKCdjaGVja19mb3JtX3JvZG5lX2Npc2xvJzo6cmVn cHJvYyk7DQogcGdfZ2V0X2Z1bmN0aW9uX2FyZ3VtZW50cw0K4pSA4pSA4pSA4pSA4pSA4pSA4pSA 4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA4pSA 4pSA4pSADQogY2hhcmFjdGVyIHZhcnlpbmcsIGJvb2xlYW4NCigxIHJvdykNCg0KcmVnYXJkcw0K DQpQYXZlbCBTdGVodWxlDQoNCj4NCj4NCj4gVGhhbmtzIGluIEFkdmFuY2UgZm9yIGFueSBoZWxw Lg0K