query to find all dblink()

Question

Besides manually searching for all the dblink() used in triggers/functions/stored procedures is there a way to query for this information?

Example: There is a dblink inside of a trigger, can I select something from the postgres schema that would identify this?

I'm thinking this is a manual effort but asking just to make sure I'm not missing something.

NOTE: I have some logging where I am also looking for dblink()

Erwin Brandstetter · Accepted Answer · 2024-04-15T06:07:29.413

4

I would draw a text backup and search it with vim or any tool of your choice.

As far as PL/pgSQL functions are concerned (there is one behind every trigger), you can query the system catalog pg_proc:

SELECT *
FROM   pg_catalog.pg_proc
WHERE  prosrc ILIKE '%dblink%';

edited Apr 15 '24 at 06:07

answered Feb 23 '15 at 21:30

Erwin Brandstetter

185,527
28
463
633

score 3 · Answer 2 · edited Feb 13 '21 at 11:43

3

select * from information_schema._pg_foreign_servers;

OR

select * from pg_foreign_server;

edited Feb 13 '21 at 11:43

Colin 't Hart

9,455
15
36
44

answered Oct 21 '18 at 14:15

user115223

85
1
5

score 1 · Answer 3 · edited Apr 13 '17 at 12:42

You can get all open dblink connections using dblink_get_connections().

Those that are not used can only be found by examining your function code like Erwin suggests. I thought that with SQL functions one can query for the dblink functions it depends on, but it happens not to be the case (actually, you can even drop the extension while having functions using dblink_connect(), for example.)

query to find all dblink()

3 Answers3