PostgreSQL DBLink: No function matches the given name and argument types

Question

I was playing around with DBLINK and I wanted to try it. So I run this simple query

CREATE EXTENSION dblink;

SELECT *  
FROM dblink(('dbname=genesis_admin')::text,
      ('SELECT * FROM user_account')::text);

then to my surprise

[WARNING  ] CREATE EXTENSION dblink
            ERROR:  extension "dblink" already exists
[WARNING  ] SELECT *  FROM dblink(('dbname=genesis_admin')::text, ('SELECT * FROM user_account')::text)
            ERROR:  function dblink(text, text) does not exist
            LINE 1: SELECT *  FROM dblink(('dbname=genesis_admin')::text, ('SELE...
                                   ^
            HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

How can it not be existing if it already exists?

Answer 1

I encountered the same error and the reason is that this is because DBLink gets installed (by default) in Public schema and you have probably modified the search_path to a list that doesn't include public). The Database although contains the function DBlink(text, text) is therefore unable to find that function.

To get this to work, you need to add specific schema to the DBlink function call.

SELECT public.dblink(xxx, yyy);