3

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

1

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

As far as plpgsql 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%';
  • Thanks a combination of grep and this helped my efforts! – Phill Pafford Feb 27 '15 at 4:02
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.)

  • 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. – Erwin Brandstetter Feb 27 '15 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... – dezso Mar 6 '15 at 8:41
-2
select * from information_schema._pg_foreign_servers;

OR

select * from pg_foriegn_servers;
  • Foreign servers are something different than dblink() calls – a_horse_with_no_name Oct 22 '18 at 13:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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