正在创建postgresql存储过程

Question

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'

此查询从数据库#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'

此查询从数据库#2 返回每个昨天的特定数据

1. 如何从这些请求中生成存储过程
2. 这两个查询(可能通过一个过程)应该相互比较(仅根据其中的行数)
3. 如果这两个查询相等，则应显示第一个查询(来自数据库#1)的数据+"true"。如果它们的行数不相等，我想得到"false"+还得到查询数据(数据库#1)

我需要这个来发送报告。如果在比较两个请求时，我得到"true"，则会发送此报告，否则不会发送报告。

Answer 1

您可以用各种语言创建存储过程。有关plpgsql，请参阅此处的文档。基本上适用于您的SQL1代码：

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

一旦创建了存储过程sql1和sql2，就可以将结果与连接进行比较：

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)