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

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.

2011/1/5 flying eagle <eagleinfly@gmail.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