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