Cross database select postgresql (dblink )

Question

How can I run a stored procedure from database1 - sp1, using dblink, join to another stored procedure - sp2, from database2 something to get the output data where price.sp1 != price.sq2.

Something like this:

select * 
from sq1 
inner join dblink('dbname=name', 'select * from sq2') 
where price.sp1 != price.sq2

I need this so that if the data of the "price" column (table1) of database1 differs from the data of the "price"(table2) of database2 column, get this mismatched data.

I tried to do something like this but I get an error:

ERROR: syntax error at end of input
LINE 1: ... inner join dblink('dbname=paymentdb', 'select * from sq2')
SQL state: 42601
Character: 78

These two databases have different names, but they have the same api and port

Stored procedure 1

CREATE OR REPLACE FUNCTION sql2 
( IN Name character varying
, IN InStatus character varying
, OUT Date timestamp without time zone
, OUT Price numeric
, OUT Status character varying
, OUT Service integer
, OUT Agent bigint
, OUT Identifier character varying
)
RETURNS setof record LANGUAGE plpgsql AS
$$
BEGIN
RETURN QUERY
SELECT pp.created_dt AS "Date", 
       pp.priceAS "Price", 
       pp.status_id AS "Status", 
       pp.service_id AS "Service", 
       pp.agent_id AS "Agent", 
       pp.external_id AS "Identifier"
  FROM payments AS pp
 WHERE pp.service_id IN (009, 010) 

   AND pp.created_dt::date = (CURRENT_DATE - INTERVAL '1' day)::date
   AND pp.status_id = 'SUCCESS'; 
END ;
$$

Stored procedure 2

CREATE OR REPLACE FUNCTION sql1 
( IN Name character varying
, IN InStatus character varying
, OUT Date character varying
, OUT Timee time without time zone
, OUT Initiator character varying
, OUT Service character varying
, OUT Price numeric
, OUT Status character varying
, OUT Props character varying
, OUT Identifier character varying
, OUT External_status character varying
)
RETURNS setof record LANGUAGE plpgsql AS
$$
BEGIN
RETURN QUERY
SELECT pp.id, 
       TO_CHAR(pp.created_dt::date, 'dd.mm.yyyy') AS "Date", 
       CAST(pp.created_dt AS time(0)) AS "Timee", 
       au.username AS "Initiator", 
       ss.name AS "Service", 
       pp.price AS "Price", 
       REPLACE(pp.status, 'SUCCESS', 'Good') AS "Status", 
       pp.account AS "Props", 
       pp.external_id AS "Identifier", 
       COALESCE(pp.external_status, null, 'Status') AS "External_status"
  FROM payments_pay AS pp
 INNER JOIN auth_user AS au ON au.id = pp.creator_id
 INNER JOIN services_service AS ss ON ss.id = pp.service_id
 WHERE pp.created_dt::date = (CURRENT_DATE - INTERVAL '1' day)::date
                
   AND ss.name IN ('Some name', 'Some name') AND pp.status = 'SUCCESS';
   END;
   $$

If you need that a lot, a foreign table (through a foreign data wrapper) will be much easier to handle (and probably faster as well) — user330315
– user330315, Commented Jan 28, 2022 at 8:41

Serg · Accepted Answer · 2022-01-28 09:06:44Z

3

From the manual

The function returns the row(s) produced by the query. Since dblink can be used with any query, it is declared to return record, rather than specifying any particular set of columns. This means that you must specify the expected set of columns in the calling query — otherwise PostgreSQL would not know what to expect.

Also looks like your column references are erroneously reversed, must be TableAlias.ColumnName

select * 
from sq1 
inner join dblink('dbname=name', 'select * from sq2') sq2(..your columns here including price..) 
  on ..your join criteria here..
where sq1.price != sq2.price

edited Jan 28, 2022 at 9:06

answered Jan 28, 2022 at 8:43

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Alexander Over a year ago

select * from sq1 inner join dblink('dbname=baseName', 'select * from sq2') sq2(created_dt, price, status_id) on sq1.id = sq2.external_id where sq1.price!= sq2.price

Alexander Over a year ago

I tried to do so. But I'm getting an error: ERROR: relation "sq1" does not exist LINE 2: from sq1 ^ SQL state: 42P01 Character: 16

Serg Over a year ago

We hardly can guess what is the real table you want instead of sq1.

Alexander Over a year ago

I added my question

Collectives™ on Stack Overflow

Cross database select postgresql (dblink )

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related