Postgresql – query to find all dblink()

dblinkpostgresql

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

Best Answer

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%';

Related Solutions

PostgreSQL: DBLink weird permission/connection error

Here is what I think is happening. I don't think it is a bug.

You have a view. That view is somewhat complex but it is a dynamically rewritten query.
The planner is pretty smart. It will ignore UNION clauses where the search criteria will not produce an output result.
You are not executing what you think you are. (EXPLAIN ANALYSE should show you this)

My recommendations:

Wrap the whole result set in a plpgsql function. Wrap that function in the view. OR
Use a WITH clause. I am less sure about this one. I think the with clause might result in running the whole thing consistently but not 100% sure, and it is not guaranteed in future versions. Therefore wrapping in a function is probably better.

I think what the planner is seeing is:

"Oh we don't need to execute the first and third portions of the view because they are not in the output! Let's skip and only run the second portion!" This is a feature, not a bug (and it is what allows table partitioning to be useful).

PostgreSQL trigger function that selects from %current% table

I suggested that you use trigger arguments, but it's actually not necessary. You can use the automatic variables TG_TABLE_SCHEMA and TG_TABLE_NAME, or use TG_RELID. These, alongside EXECUTE for dynamic SQL, let you do what you want:

BEGIN
    EXECUTE format('SELECT colname FROM %I', TG_RELID)
END;

BEGIN
    EXECUTE format('SELECT colname FROM %I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME)
END;

(Of course these won't work as-is, since the SELECT has no destination for the data. You have to use EXECUTE format(..) INTO ... to store the result into a DECLAREd variable), e.g.

DECLARE
    _colvar integer;
BEGIN
    EXECUTE format('SELECT colname FROM %I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME) INTO _colvar;
    RAISE NOTICE 'colname value was %',_colvar;
END;

Best Answer

Related Solutions

PostgreSQL: DBLink weird permission/connection error

PostgreSQL trigger function that selects from %current% table

Related Question