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
