Обсуждение: How to find details of arguments in all functions in postgre - One solution

Поиск
Список
Период
Сортировка

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

От
venkatrao.b@tcs.com
Дата:

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

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

От
Michael Wood
Дата:
On 6 March 2010 12:11,  <venkatrao.b@tcs.com> wrote:
>
> 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).
[...]
>
> 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.

Does this give you what you want?

$ psql -c '\df schemaname.*' -h servername dbname username

If so, you can use the -E option to get psql to tell you the SQL
statement it uses to generate those results.

i.e.:

$ psql -E -c '\df schemaname.*' -h servername dbname username

--
Michael Wood <esiotrot@gmail.com>