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

Question

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.

Thanks, I have done that as part of db setup. And this error still happens. — HuFlungPu, Jun 19 '18 at 20:19
The problem was that dblink was not created in public schema as expected. so have to qualify with someschema.dblink(...) — HuFlungPu, Jun 19 '18 at 20:35
Does this answer your question? [dblink does not exist even when the extension already exists?](https://stackoverflow.com/questions/46557439/dblink-does-not-exist-even-when-the-extension-already-exists) — Maksim Vi., Nov 15 '19 at 06:35

score 51 · Answer 1 · answered Aug 05 '19 at 12:08

51

You need to install an extension dblink

create extension dblink;

answered Aug 05 '19 at 12:08

Roman Marusyk

23,328
24
73
116

score 8 · Answer 2 · answered May 13 '20 at 11:44

8

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

answered May 13 '20 at 11:44

Vzzarr

4,600
2
43
80

and in my case it was not even "public". So thank you! – Слава ЗСУ Jan 14 '21 at 16:57
how to install "create extension dblink" by using python code? please suggest. – Hushen Apr 23 '21 at 16:09
@Hushen dblink is a function available in Postgres, I don't know what exactly you mean by "installing using python code" – Vzzarr Apr 24 '21 at 19:55

score 5 · Answer 3 · answered Mar 08 '21 at 14:43

5

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

answered Mar 08 '21 at 14:43

Duilio

876
1
10
15

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

3 Answers3

Linked