How to combine result of two query in single return table in postgres

Question

Hello friends,
i am createing one function in which i am using dblink for connect two diffrenet database
this is the smeple

SELECT *
FROM dblink('host=localhost port=5432 dbname=DB_Client user=postgres password=sa123','select "ClientId","StreetOne","StreetTwo" from "ClientAddress"')
AS c(id int,StreetOne text,StreetTwo text);

SELECT *
FROM dblink('host=localhost port=5432 dbname=DB_Quotes user=postgres password=sa123','select q."QuoteId",q."CreatedDate" ,q."Description",q."RateOpportunity",(p."Qty" * p."UnitCost") Total
FROM "Quote" q
INNER JOIN "ProductOrservice" p
ON q."QuoteId" = p."QuoteId"
where q."ClientId"=1')
AS c(quoteid integer,createddate character varying, description character varying, rateopportunity character varying, total integer);

This both query return table but i want to combine them in one table and return as result.

Suraj Dubey · Answer

dear, muhammad i am working in multitenant microservice project in which i am using postgressql i am trying to achive the goal that execute multiple database query using dblink and return in single table. Note:- every query result is diffrent. and i want to combine them in single result. kindly suggest the idea on that. thanks.

Muhammad Imran Ansari · Answer

Create a View and combine both results into one. If both tables have identical structure then simply use UNION operator to combine result sets of two or more SELECT statements into a single. CREATE VIEW FinalView AS SELECT * FROM dblink( 'host=localhost port=5432 dbname=DB_Client user=postgres password=sa123' , 'select "ClientId","StreetOne","StreetTwo" from "ClientAddress"' ) AS c(id int ,StreetOne text,StreetTwo text); UNION SELECT * FROM dblink( 'host=localhost port=5432 dbname=DB_Quotes user=postgres password=sa123' ,' select q. "QuoteId" ,q. "CreatedDate" ,q. "Description" ,q. "RateOpportunity" ,(p. "Qty" * p. "UnitCost" ) Total FROM "Quote" q INNER JOIN "ProductOrservice" p ON q. "QuoteId" = p. "QuoteId" where q. "ClientId" =1') AS c(quoteid integer ,createddate character varying , description character varying , rateopportunity character varying , total integer ); Note: If both tables have different structure then replace SELECT * with SELECT field1, field2, ... in both queries.