Re: Can you please let me know?

2010/4/14 Gaurav K Srivastav <gauravgkp(at)gmail(dot)com>:
> Hi Sir,
>
> Suppose I created a function getage(id character varying).
>
> Now Is there any query in postgreSQL that I can retreive the name of all
> user difned/system function in the database schema.
> like "select VIEW_TYPE_OWNER, VIEW_TYPE, OID_TEXT from user_views" will list
> all views in oracle.
> or
> select object_name,CREATED,OBJECT_TYPE from user_objects;
>
> so that I can get the name of all objects created in a database schema.

>
> If yes Please help me out or is there any tutorial online please give me URL
> for the same.

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",
CASE
WHEN p.proisagg THEN 'agg'
WHEN p.proiswindow THEN 'window'
WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
ELSE 'normal'
END as "Type"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname ~ '^(public)$' -- << put here your schema
ORDER BY 1, 2, 4;

http://www.postgresql.org/docs/8.4/static/catalog-pg-proc.html

Regards
Pavel Stehule
>
>
> --
> Thanks & Regards
> Gaurav K Srivastav