Re: [BUGS] Can you please let me know?

Hello

2010/4/14 Gaurav K Srivastav <gauravgkp(at)gmail(dot)com>:
> 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(dot)stehule(at)gmail(dot)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(at)gmail(dot)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(dot)stehule(at)gmail(dot)com>
>> > wrote:
>> >>
>> >> 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
>> >
>> >
>> >
>> > --
>> > Thanks & Regards
>> > Gaurav K Srivastav
>> >
>
>
>
> --
> Thanks & Regards
> Gaurav K Srivastav
>