在PL/pgSQL中使用动态查询创建一个数组

Question

我正在尝试在plpgsql函数中使用动态选择查询创建一个数组，但不幸的是，我遇到了语法错误。

ERROR:  syntax error at or near "EXECUTE"
ZEILE 19:  SELECT DISTINCT INTO outputIdsToDelete ARRAY( EXECUTE findA...
                                                         ^

有人可以帮我吗？ 这是函数本身：

CREATE OR REPLACE FUNCTION deleteMAPPINGs(
   mapTable1_key 文本, mapping_table 文本, mapTable2_key 文本,
   table2 文本, table2_key 文本,
   inputIdsToDelete bigint[]) RETURNS bigint[] AS
$BODY$
DECLARE
    outputIdsToDelete bigint[];
    findAllQuery 文本;
    findUnreferencedQuery 文本;
    BEGIN
    findAllQuery := 'SELECT DISTINCT ' || mapTable2_key ||
        ' FROM '  || mapping_table ||
        ' WHERE ' || mapTable1_key || ' = ANY(inputIdsToDelete)';
    findUnreferencedQuery := 'SELECT DISTINCT ' || table2_key || --查找未使用的
        ' FROM ' || table2 ||
        ' WHERE ' || table2_key || ' NOT IN (' ||
            'SELECT DISTINCT ' || mapTable2_key || --所有已使用的
            ' 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 --覆盖未使用的
        ARRAY(EXECUTE findUnreferencedQuery);
RETURN outputIdsToDelete;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

- Veselin

Answer 1

您不能在SQL语句中使用EXECUTE - EXECUTE仅限于plpgsql语句，因此不能在SQL语句内部使用 -

EXECUTE string INTO variable

- Pavel Stehule

---

我明白了，谢谢。那么我如何从一个EXECUTE查询中创建一个数组呢？ - Veselin

请注意 - 您的查询不容易受到 SQL 注入攻击 - 您应该使用 quote_ident 函数来安全引用动态 SQL 中的标识符。翻译后的内容：执行“SELECT ARRAY(SELECT mycolumn FROM mytable)”并将结果存储到变量 avar 中，或者在您的示例中执行“SELECT ARRAY(' || findAllQuery || ')'”并将结果存储到变量中。请注意，您的查询不容易受到 SQL 注入攻击，但为了安全起见，建议您在动态 SQL 中引用标识符时使用 quote_ident 函数。 - Pavel Stehule

非常感谢！这个可行 :). 我明白你的意思是我的查询容易受到注入攻击，我应该将其重写为findAllQuery := 'SELECT DISTINCT ' || quote_ident(mapTable2_key) || ' FROM ' || quote_ident(mapping_table) || ' WHERE ' || quote_ident(mapTable1_key) || ' = ANY(inputIdsToDelete)';？现在我的唯一问题是如何将我的输入数组inputIdsToDelete绑定到选择查询中。我得到了另一个错误，像这样的错误：Error: Column inputIdsToDelete does not exist. 我应该开一个新的问题还是你能在这里帮助我？ - Veselin

你可以使用以下两种方式之一来编写与程序相关的内容：

1. 使用USAGE子句：- EXECUTE '..=ANY($1)' INTO ... USING array_variable
2. 或者使用基于字符串的解决方案：|| ' = ANY(''' || inputIdsToDelete::text || ''')'

- Pavel Stehule