Creating a postgresql stored procedure-CodePudding

SQL1

SELECT pp.id, 
       TO_CHAR(pp.created_dt::date, 'dd.mm.yyyy') AS "Date", 
       CAST(pp.created_dt AS time(0)) AS "Time", 
       au.username AS "Initiator", 
       ss.name AS "Service", 
       pp.amount AS "Amount", 
       REPLACE(pp.status, 'SUCCESS', 'Success') AS "Status", 
       pp.account AS "Props", 
       pp.external_id AS "Identifier", 
       COALESCE(pp.external_status, null, 'Indefined')  AS "External status"
  FROM payments AS pp
 INNER JOIN auth_user AS au ON au.id = pp.creator_id
 INNER JOIN services AS ss ON ss.id = pp.service_id
 WHERE pp.created_dt::date = (CURRENT_DATE - INTERVAL '1' day)::date
        
  AND ss.name = 'SomeName' AND pp.status = 'SUCCESS'

This query returns specific data for each yesterday from database # 1

SQL2

SELECT pp.created_dt, pp.amount, pp.status_id, pp.service_id, pp.agent_id, pp.external_id
  FROM payment AS pp
 WHERE pp.service_id = 1
   AND pp.created_dt::date = (CURRENT_DATE - INTERVAL '1' day)::date
   AND pp.status_id = 'SUCCESS'

This query returns specific data for each yesterday from database # 2

How can I make a stored procedure out of these requests?
These two queries (possibly through a procedure) should be compared with each other (only by the number of lines in them).
If these two queries are equal, the data of the first query (from database # 1) 'true' should be displayed. In the event that they are not equal in the number of rows - I want to get 'false' also get the query data (database # 1).

I need this to send reports. If, when comparing two requests with each other, I get 'true' - this report will be sent, otherwise the report will not be sent.

CodePudding user response：

You can create stored procedures in various languages. For plpgsql see the documentation here. Basically for your SQL1 code :

CREATE OR REPLACE FUNCTION sql1 
( IN Name varying character
, IN InStatus varying character
, OUT Date varying character
, OUT Time time
, OUT Initiator varying character
, OUT Service varying character
, OUT Amount varying character
, OUT Status varying character
, OUT Props varying character
, OUT Identifier varying character
, OUT External_status varying character
)
RETURNS setof record LANGUAGE plpgsql AS
$$
BEGIN
RETURN QUERY
SELECT ... ; -- copy here your SQL1 code
END ;
$$

Once you have created both stored procedures sql1 and sql2, you can compare the result with a join :

SELECT CASE WHEN count(*) OVER() = s2.ct THEN true ELSE false END, s1.*
FROM slq1(sql1_parameters) AS s1
CROSS JOIN (SELECT count(*) FROM slq2(sql2_parameters)) AS s2(ct)