CODEWITHSUNDEEP
sqlpostgresql
Alexander
Alexander

Reputation: 147

Cross database select postgresql (dblink )

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;
   $$

Upvotes: 0

Views: 1648

Answers (1)

Serg
Serg

Reputation: 22811

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

Upvotes: 3

Related Questions