CODEWITHSUNDEEP
sqlpostgresql
Alexander
Alexander

Reputation: 147

Postgresql dblink ERROR: function does not exist

I wanted to call a function from another database using dblink

Created a connection

select dblink_connect('b_dblink','hostaddr=someHost dbname=paydb user=user password=pass');

After that I try to call a function located in another database

select * 
from dblink('b_dblink', 'select * from report_prov()') 
as t(id bigint, created_dt timestamp without time zone, amount numeric, status_id character varying, service_id integer, agent_id bigint, external_id character varying);

Then I get an ERROR

ERROR:  function report_prov() does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
CONTEXT:  Error occurred on dblink connection named "b_dblink": could not execute query.
SQL state: 42883

The function itself (select * from report_prov();), works well for me. But if I try to call it via dblink, I get an error

Upvotes: 0

Views: 1533

Answers (1)

Saitejareddy
Saitejareddy

Reputation: 361

After executing this:

select dblink_connect('b_dblink','hostaddr=someHost dbname=paydb user=user password=pass');

You should also create wrapper as mentioned below:

SELECT dblink_connect('dbname=postgres options=-csearch_path=');
 dblink_connect
----------------
 OK
(1 row)

SELECT dblink_connect('myconn', 'dbname=postgres options=-csearch_path=');
 dblink_connect
----------------
 OK
(1 row)

-- FOREIGN DATA WRAPPER functionality
-- Note: local connection must require password authentication for this to work properly
--       Otherwise, you will receive the following error from dblink_connect():
--       ERROR:  password is required
--       DETAIL:  Non-superuser cannot connect if the server does not request a password.
--       HINT:  Target server's authentication method must be changed.

CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr '127.0.0.1', dbname 'contrib_regression');

CREATE USER regress_dblink_user WITH PASSWORD 'secret';
CREATE USER MAPPING FOR regress_dblink_user SERVER fdtest OPTIONS (user 'regress_dblink_user', password 'secret');
GRANT USAGE ON FOREIGN SERVER fdtest TO regress_dblink_user;
GRANT SELECT ON TABLE foo TO regress_dblink_user;

\set ORIGINAL_USER :USER
\c - regress_dblink_user
SELECT dblink_connect('myconn', 'fdtest');
 dblink_connect 
----------------
 OK
(1 row)

SELECT * FROM dblink('myconn', 'SELECT * FROM foo') AS t(a int, b text, c text[]);
 a  | b |       c       
----+---+---------------
  0 | a | {a0,b0,c0}
  1 | b | {a1,b1,c1}
  2 | c | {a2,b2,c2}
  3 | d | {a3,b3,c3}
  4 | e | {a4,b4,c4}
  5 | f | {a5,b5,c5}
  6 | g | {a6,b6,c6}
  7 | h | {a7,b7,c7}
  8 | i | {a8,b8,c8}
  9 | j | {a9,b9,c9}
 10 | k | {a10,b10,c10}
(11 rows)

reference for the above code is here

And as you trying to access the function from the other db, you need to give this function permission to the user as show below. assuming user name as my_user.

GRANT USAGE ON SCHEMA my_schema TO my_user;

The above command allows the execution of functions but not the access to tables. So, if my_user executes the function, it still produces an access error, if the function accesses tables etc. in my_schema. To avoid this, you may define your function as security definer:

ALTER FUNCTION my_schema.my_function(...) SECURITY DEFINER SET search_path = public;

Also an other way to give execute permission to specific users::

REVOKE ALL ON FUNCTION my_schema.my_function(...) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION my_schema.my_function(...) TO my_user;

Upvotes: 1

Related Questions