Skip site navigation (1) Skip section navigation (2)

How to find details of arguments in all functions in postgre - One solution

From: venkatrao(dot)b(at)tcs(dot)com
To: pgsql-novice(at)postgresql(dot)org,pgsql-general(at)postgresql(dot)org
Subject: How to find details of arguments in all functions in postgre - One solution
Date: 2010-03-06 10:11:40
Message-ID: OFE7C9FD2D.81BA31E7-ON652576DE.003771E2-652576DE.00380050@tcs.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novice
Hello All,

I was trying to find some way, to list out all argument details in all 
functions in postgre(input parameter names, types and mode - in/out).
 I found one solution - 

------------------------------------------------------------------------------------------------------
select r.routine_schema,b.typname,t.* from
(
select proname,proallargtypes[idx] argType, proargnames[idx] 
argName,proargmodes[idx] argMode
from pg_proc a
inner join generate_series(1,50)idx on 1 = 1 

-- is this correct way, i dont know.

)t 
inner join pg_type b on b.typelem = t.argType
inner join information_schema.routines r on r.routine_name = t.proname
where t.argType is not null 
order by r.routine_schema,t.proname

--------------------------------------------------------------------------------------------


Please suggest, if some better way is available of achieving this.
Also, i want to know the way i am getting type of argument is right or 
not?
Background - We have migrated one oracle database to postgre. Now from 
.Net for coding , we need a list of all functions and their types.

Thanks in advance


Regards,
Venkat
=====-----=====-----=====
Notice: The information contained in this e-mail
message and/or attachments to it may contain 
confidential or privileged information. If you are 
not the intended recipient, any dissemination, use, 
review, distribution, printing or copying of the 
information contained in this e-mail message 
and/or attachments to it are strictly prohibited. If 
you have received this communication in error, 
please notify us by reply e-mail or telephone and 
immediately and permanently delete the message 
and any attachments. Thank you


Responses

pgsql-novice by date

Next:From: Michael WoodDate: 2010-03-06 11:52:43
Subject: Re: [NOVICE] How to find details of arguments in all functions in postgre - One solution
Previous:From: Scott GellerDate: 2010-03-06 01:30:55
Subject: $libdir not working

pgsql-general by date

Next:From: Michael WoodDate: 2010-03-06 11:52:43
Subject: Re: [NOVICE] How to find details of arguments in all functions in postgre - One solution
Previous:From: Noah MischDate: 2010-03-06 04:02:26
Subject: 9.0 VACUUM FULL vs. ALTER TABLE?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group