我试图在plpgsql函数中创建一个带有动态选择查询的数组 . 不幸的是我收到了语法错误 .
ERROR: syntax error at or near "EXECUTE"
ZEILE 19: SELECT DISTINCT INTO outputIdsToDelete ARRAY( EXECUTE findA...
^
有人可以帮帮我吗？这是函数本身：
CREATE OR REPLACE FUNCTION deleteMAPPINGs(
mapTable1_key text, mapping_table text, mapTable2_key text,
table2 text, table2_key text,
inputIdsToDelete bigint[]) RETURNS bigint[] AS
$BODY$
DECLARE
outputIdsToDelete bigint[];
findAllQuery text;
findUnreferencedQuery text;
BEGIN
findAllQuery := 'SELECT DISTINCT ' || mapTable2_key ||
' FROM ' || mapping_table ||
' WHERE ' || mapTable1_key || ' = ANY(inputIdsToDelete)';
findUnreferencedQuery := 'SELECT DISTINCT ' || table2_key || --find unused
' FROM ' || table2 ||
' WHERE ' || table2_key || ' NOT IN (' ||
'SELECT DISTINCT ' || mapTable2_key || --all used
' FROM ' || mapping_table || ')';
SELECT DISTINCT INTO outputIdsToDelete ARRAY( EXECUTE findAllQuery );
DELETE FROM mapping_table WHERE
mapTable1_key = ANY(inputIdsToDelete) AND
mapTable2_key = ANY(outputIdsToDelete);
SELECT DISTINCT INTO outputIdsToDelete --overwrite with unused
ARRAY(EXECUTE findUnreferencedQuery);
RETURN outputIdsToDelete;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
1 回答
你不能在SQL语句中使用EXECUTE - EXECUTE只是plpgsql语句，所以它不能在SQL里面 -