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

Thanks Filip Rembiałkowski, that's exactly what I want.

2011/1/6 Filip Rembiałkowski <plk(dot)zuber(at)gmail(dot)com>

>
>
> 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
>
>

--
Xu Wei

Email: eagleinfly(at)gmail(dot)com
MSN : efisky(at)hotmail(dot)com
Skype: eagleinfly