Re: How can I get the list of index(or other objects) which depends on a function

2011/1/5 flying eagle <eagleinfly(at)gmail(dot)com>

> I want to get all the dependencies of a table, I know how to get the index
> list using sql, but I don't know how to get the list of objects who using a
> function, for example:
>
> CREATE OR REPLACE FUNCTION reverse_last_64(TEXT) RETURNS TEXT AS $$
> SELECT
> array_to_string(
> ARRAY
> ( SELECT substring($1, s.i,1) FROM generate_series(length($1),
> greatest(length($1) - 64 + 1, 1), -1) AS s(i) ),
> '');
> $$ LANGUAGE SQL IMMUTABLE"
>
> CREATE TABLE Test2(id BIGSERIAL PRIMARY KEY, name varchar(100))
> CREATE INDEX idx_test2_name ON test2(reverse_last_64(name)
> varchar_pattern_ops)
>
> drop function reverse_last_64(TEXT) ;
>
> will show
> ERROR: cannot drop function reverse_last_64(text) because other objects
> depend on it
> DETAIL: index idx_test2_name depends on function reverse_last_64(text)
> HINT: Use DROP ... CASCADE to drop the dependent objects too.
>
> what I want to get is the list of index(or maybe other function) which
> using the function using sql.
>
>

I hope this will help you:

select * from pg_depend where refobjid = (select oid from pg_proc where
proname='reverse_last_64');

http://www.postgresql.org/docs/9.0/interactive/catalog-pg-depend.html

greets,
Filip