Can you please let me know?

--
Thanks & Regards
Gaurav K Srivastav

And thanks for the reply I got my result.

Gaurav Kumar Srivastav

On Wed, Apr 14, 2010 at 7:00 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hello

please, try to use

SELECT n.nspname as "Schema",
 p.proname as "Name",
 CASE WHEN p.proretset THEN 'setof ' ELSE '' END ||
 pg_catalog.format_type(p.prorettype, NULL) as "Result data type",
 CASE WHEN proallargtypes IS NOT NULL THEN
   pg_catalog.array_to_string(ARRAY(
     SELECT
       CASE
         WHEN p.proargmodes[s.i] = 'i' THEN ''
         WHEN p.proargmodes[s.i] = 'o' THEN 'OUT '
         WHEN p.proargmodes[s.i] = 'b' THEN 'INOUT '
       END ||
       CASE
         WHEN COALESCE(p.proargnames[s.i], '') = '' THEN ''
         ELSE p.proargnames[s.i] || ' '
       END ||
       pg_catalog.format_type(p.proallargtypes[s.i], NULL)
     FROM
       pg_catalog.generate_series(1,
pg_catalog.array_upper(p.proallargtypes, 1)) AS s(i)
   ), ', ')
 ELSE
   pg_catalog.array_to_string(ARRAY(
     SELECT
       CASE
         WHEN COALESCE(p.proargnames[s.i+1], '') = '' THEN ''
         ELSE p.proargnames[s.i+1] || ' '
         END ||
       pg_catalog.format_type(p.proargtypes[s.i], NULL)
     FROM
       pg_catalog.generate_series(0,
pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)
   ), ', ')
 END 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 NULL
     OR   p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)
     AND NOT p.proisagg
 AND n.nspname ~ '^(public)$'
ORDER BY 1, 2, 3, 4;

it is for 8.3.

p.s. This isn't bug. Please use another mailing list - pgsql-general
will be better

Regards
Pavel Stehule

2010/4/14 Gaurav K Srivastav <gauravgkp@gmail.com>:

Hi Pavel ,
I am running

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

and getting
ERROR:  function pg_catalog.pg_get_function_result(oid) does not exist
LINE 3:  pg_catalog.pg_get_function_result(p.oid) as "Result data ty...
         ^
HINT:  No function matches the given name and argument types. You might
need
to add explicit type casts.
********** Error **********
ERROR: function pg_catalog.pg_get_function_result(oid) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might
need
to add explicit type casts.
Character: 54

as output.

I am using PostgreSQL 8.3.3, compiled by Visual C++ build 1400 on
windows
machine.

Gaurav Kumar Srivastava

On Wed, Apr 14, 2010 at 6:46 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2010/4/14 Gaurav K Srivastav <gauravgkp@gmail.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

--
Thanks & Regards
Gaurav K Srivastav

--
Thanks & Regards
Gaurav K Srivastav