Search Postgresql Archives

Re: How to search particular line/text code in all Postgres all database objects

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Raghavendra Rao J S V wrote:

> Hi All,
> 
> How to search particular line/text code in all Postgres all database
> object's like functions,triggers,views etc ?
> 
> Is there any data dictionary table in Postgres?
> 
> Eg:- My requirement is , I want to found data related to employee table in
> any function, trigger,view etc.
> 
> Kindly help me.
> -- 
> Regards,
> Raghavendra Rao J S V
> Mobile- 8861161425

This may not be helpful but all function, trigger and view
source code (and table definitions) should exist outside the
database in a git repository or similar and that can be
searched. Otherwise, you probably can't audit whether or
not the code in the database is up to date because you have
nothing to compare it to.

I only use functions and the code for them resides in pg_proc.
My tool for auditing functions contains this query which might
be a starting point for you:

        select
            p.proname, -- name
            p.proretset, -- returns setof?
            p.proisstrict, -- strict 't' or 'f'
            p.provolatile, -- volatile or stable 'v' or 's'
            p.prosecdef, -- security definer 't' or 'f'
            p.pronargs, -- number of in arguments
            p.prorettype, -- return type
            p.proargtypes, -- space-separated list of in arg types
            p.proallargtypes, -- array of in/out arg types (iff there are out args)
            p.proargmodes, -- array of in/out arg modes like {i,o,o} (iff there are out args)
            p.proargnames, -- array of in/out arg names like {id,code,name}
            p.prosrc, -- source code
            cast(cast(p.oid as regprocedure) as text) -- nice signature
        from
            pg_user u,
            pg_proc p
        where
            u.usename = current_user and
            p.proowner = u.usesysid and
            p.proname like 'aps%' -- 'aps' is the prefix for our functions
        order by
            p.proname

Or just: select proname, prosrc from pg_proc where proname like ...

cheers,
raf





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux