Hello 2010/4/14 Gaurav K Srivastav <gauravgkp@xxxxxxxxx>: > Hi Pavel , > > First of all I am sorry for this to post on bugs, can you please place it > into pgsql-general maling list . > To get list of views where I have to change the query? can you please let > me know in which table/view the object id and object type/name stored? so > that in future I can do such tasks myself without disturbing you. > it is one functionality of psql console when you ran it with parameter -E, then all SQL used for processing of command are showed. psql -E postgres create view f as select 10; you can try \dv public.* \d f Regards Pavel Stehule > 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@xxxxxxxxx> > 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@xxxxxxxxx>: >> > >> > 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@xxxxxxxxx> >> > wrote: >> >> >> >> 2010/4/14 Gaurav K Srivastav <gauravgkp@xxxxxxxxx>: >> >> > 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 > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
