ERROR: function dblink(unknown, unknown) does not exist

17
votes

I have defined a foreign server pointing to another database. I then want to execute a function in that database and get back the results.

When I try this:

SELECT * FROM  dblink('mylink','select someschema.somefunction(''test'', ''ABC'')')

or this:

SELECT t.n FROM  dblink('mylink', 'select * from someschema.mytable') as t(n text)

I get the error:

ERROR: function dblink(unknown, unknown) does not exist

Running as superuser.

3
postgresqldblink
create extension dblink; - Vao Tsun
Thanks, I have done that as part of db setup. And this error still happens. - HuFlungPu
ran on same db?.. - Vao Tsun
select * from pg_available_extensions shows it. - HuFlungPu
The problem was that dblink was not created in public schema as expected. so have to qualify with someschema.dblink(...) - HuFlungPu

3 Answers

25
votes

You need to install an extension dblink

create extension dblink;
6
votes

In my case (as reported also in @HuFlungPu comments) the problem was that I initially created the dblink in public schema. I executed a SET search_path TO my_schema because I was working on my_schema; so when querying the dblink I was receiving 

ERROR: function dblink(unknown, unknown) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

After executing a SET search_path TO public (where 'public' is the schema in which the dblink was created) I was able to query again successfully through the dblink

0
votes

In my case the problem was that I was using a different user than the one I used to create the extension.

I solved it using the same user that ran the create extension dblink; command