6

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

0

3 Answers 3

2

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%';
1
  • Thanks a combination of grep and this helped my efforts! Commented Feb 27, 2015 at 4:02
2
select * from information_schema._pg_foreign_servers;

OR

select * from pg_foreign_server;
2
  • Foreign servers are something different than dblink() calls
    – user1822
    Commented Oct 22, 2018 at 13:45
  • Although @a_horse_with_no_name is technically correct, this information was exactly what I was looking for.
    – SebastianH
    Commented Feb 13, 2021 at 11:11
1

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

2
  • Objects used in the function body of a plpgsql function do not get dependency entries in pg_depend (like it happens when a view references a table). So these dependencies are not enforced by the system. Commented Feb 27, 2015 at 20:19
  • @ErwinBrandstetter yes, that was clear, but what I saw was that even SQL functions don't observe the dependency on the dblink extension's functions... Commented Mar 6, 2015 at 8:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.