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） 'true'。如果它們的行數不相等 - 我想得到 'false' 也得到查詢資料（資料庫＃1）。
我需要這個來發送報告。如果在比較兩個請求時，我得到 'true' - 將發送此報告，否則將不發送報告。
uj5u.com熱心網友回復：
您可以創建各種語言的存盤程序。對于 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)
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/331618.html
標籤：sql PostgreSQL 存储过程