以下查詢有一個 SQL 函式，其中一個引數是從SELECT陳述句中傳遞的。同時，該SELECT陳述句具有使用 function 回傳的記錄的JOIN和子句。WHEREmy_func
CREATE FUNCTION my_func(pId bigint) RETURNS TABLE (table2_id bigint, column_1 bigint, field text) AS $$
BEGIN
RETURN QUERY SELECT table2_id, table3_id, column_1, field
FROM t_name
LEFT JOIN t2_name on t_name.key = t2_name.key
WHERE t2_name.pId = pId
ORDER BY table2_id DESC
LIMIT 1;
END;$$ LANGUAGE plpgsql;
SELECT column_1, column_2,
FROM table_name
LEFT JOIN table2_name ON table2_name.table2_id = (SELECT rec.table2_id FROM my_func(table_name.column_1))
WHERE column_1 = (SELECT rec.column_1 FROM my_func(table_name.column_1))
AND table2_name.field = (SELECT rec.field FROM my_func(table_name.column_1))
GROUP BY column_1, column_2
ORDER BY column_1 ASC, column_2;
這非常有效，因為my_func有一個LIMIT 1陳述句可以很好地與上述查詢中的JOINandWHERE子句一起使用。但是，要求發生了變化，現在我需要洗掉LIMIT以便my_func回傳多條記錄。
我可以洗掉LIMIT 1函式中的陳述句，并將JOINandWHERE子句更改為如下所示：
LEFT JOIN table2_name ON table2_name.table2_id IN (SELECT rec.table2_id FROM my_func(table_name.column_1))
WHERE column_1 IN (SELECT rec.column_1 FROM my_func(table_name.column_1))
AND table2_name.field IN (SELECT rec.field FROM my_func(table_name.column_1))
我所做=的只是將符號替換為IN. 但是，即使這可行，它也不能解決我想要實作的目標，因為它為第一個子句中的每個結果和LEFT JOIN第一WHERE個子句中的每個結果以及ANDWHERE 子句中的每個結果創建一個指數查詢。顯然，不是我想要的。
我考慮過使用 aFOR LOOP作為解決方案
DO
$$
DECLARE
rec record;
BEGIN
FOR rec IN (SELECT table2_id, table3_id, column_1, field
FROM t_name
LEFT JOIN t2_name on t_name.key = t2_name.key
ORDER BY table2_id DESC)
LOOP
RETURN QUERY EXECUTE
SELECT column_1, column_2,
FROM table_name
LEFT JOIN table2_name ON table2_name.table2_id = rec.table2_id
WHERE column_1 = rec.column_1
AND table2_name.field = rec.field
GROUP BY column_1, column_2
ORDER BY column_1 ASC, column_2;
END LOOP
END;
$$
可以從以前的函式中洗掉pId不重要的原因。但是現在我遇到了錯誤：
錯誤：不能在非 SETOF 函式中使用 RETURN QUERY
關于如何實作這一目標的任何想法？簡而言之，當回傳單個記錄（強制LIMIT 1）時，該函式運行良好，但不確定如何為多個記錄重構整個事情。謝謝。
uj5u.com熱心網友回復：
@CetinBasoz 提出了一個很好的觀點，LEFT JOIN這實際上是一個INNER JOIN. 基于此以及您可以比較運算式中的整行IN這一事實，您可以像這樣簡化函式和查詢：
CREATE FUNCTION my_func(pId bigint) RETURNS TABLE (table2_id bigint, column_1 bigint, field text) AS $$
BEGIN
RETURN QUERY
SELECT table2_id, column_1, field
FROM t_name
-- LEFT Join can be commented out, since you have condition on t2_name in the WHERE clause below
-- LEFT
JOIN t2_name
on t_name.key = t2_name.key
WHERE t2_name.pId = pId
-- Below two lines can be commented out, since they don't make sense
-- ORDER BY table2_id DESC
-- LIMIT 1;
END;$$ LANGUAGE plpgsql;
SELECT column_1, column_2
FROM table_name
-- Here also, LEFT Join can be commented out, since you have condition on my_func in the WHERE clause below
-- LEFT
JOIN table2_name
ON (table2_name.table2_id, column_1, table2_name.field)
in (
SELECT table2_id, column_1, field FROM my_func(table_name.column_1)
-- this is for your previous logic, with limiting to 1 record, for testing:
-- ORDER BY table2_id DESC
-- LIMIT 1
)
GROUP BY column_1, column_2
ORDER BY column_1 ASC, column_2;
更新
我完全忽略了最后一個查詢，在哪里pId被洗掉。這樣這個函式my_func就完全不需要了，最終的查詢可以簡化為：
SELECT column_1, column_2
FROM (
SELECT table2_id, table3_id, column_1, field
FROM t_name
LEFT JOIN t2_name on t_name.key = t2_name.key
) rec
JOIN table_name on table_name.column_1 = rec.column_1
JOIN table2_name
on table2_name.table2_id = rec.table2_id
and table2_name.field = rec.field
GROUP BY column_1, column_2
ORDER BY column_1 ASC, column_2;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/505283.html
標籤：sql PostgreSQL 功能 for循环 加入