Postgres动态查询函数

Question

我需要创建一个函数，它将运行查询并返回结果，其中表名和列名是传递给函数的参数。我目前有以下代码：

CREATE OR REPLACE FUNCTION qa_scf(tname character varying, cname character varying)
RETURNS SETOF INT AS
$BODY$
BEGIN
RETURN QUERY SELECT * FROM tname WHERE cname !='AK' AND cname!='CK';
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;

运行时出现错误“关系'tname'不存在”。我是新手，正在创建Postgres函数，希望能得到任何帮助。我觉得返回的int类型是错误的，但我不知道该怎么做才能返回所有行的所有列。谢谢！

- justanother1

Answer 1

你不能像那样使用变量来替代标识符。你需要使用动态查询来实现。它会像这样：

EXECUTE 'SELECT * FROM ' || quote_ident(tname) 
        || ' WHERE ' || quote_ident(cname) || ' NOT IN (''AK'',''CK'');'
INTO result_var;

如果您使用的是 PostgreSQL 9.1 或更高版本，您可以使用 format() 函数 来更轻松地构造这个字符串。

- Matthew Wood

---

我应该将result_var声明为什么？ - justanother1

永远不要在动态 SQL 语句中插入值而不使用适当的 quote_FOO() 函数，否则你会打开注入攻击的大门。 - dbenhur

“Internal facing” 意味着任何拥有网络访问权限的人都可以掌控您的数据库。 - dbenhur

只是提醒一下，我完全不建议这样做。带有表/列参数的动态查询往往是设计不良的迹象。 - Matthew Wood

如果我正确地阅读了文档，9.1版本的format()函数中的%L和%I格式规范与quote_literal()和quote_identifier()具有相同的作用，是的，如果必须执行动态查询，则应使用这些方法。 - Matthew Wood

Answer 2

不能将表名和列名作为参数或变量指定，除非动态构建一个字符串以执行动态语句。Postgres有关于执行动态语句的优秀入门文档。重要的是要使用quote_ident()或quote_literal()正确引用标识符和文字值。format()函数可以帮助清理动态SQL语句的构造。由于你声明函数返回SETOF INTEGER，因此应选择您想要的整数字段，而不是*。

CREATE OR REPLACE FUNCTION qa_scf(tname text, cname text)
RETURNS SETOF INTEGER AS
$BODY$
BEGIN
  RETURN QUERY EXECUTE format(
    'SELECT the_integer_field FROM %I WHERE %I NOT IN (%L,  %L)',
                                   tname,   cname,    'AK', 'CK'
  );
END;
$BODY$
LANGUAGE plpgsql;

- dbenhur

---

如何使其返回所有列？ - justanother1

你可以使用 SELECT * 并声明 RETURNS SETOF RECORD。然后消费者必须理解记录元组。http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions - dbenhur

我该如何给出列定义列表？有没有办法动态地提取它们？ - justanother1

请仔细阅读那个维基链接，它解释了如何使用消费者解释记录结果。如果您确实返回多态结果，则需要进行更多的内省和动态元编程--您确定要去那里吗？请提出另一个问题来探讨这些问题。 - dbenhur

@j.gardner117 你也可以返回一个 hstore 而不是 RECORD; hstore 基本上是一个动态哈希表。 它的性能不如 RECORD，并且没有强大的数据类型，但很容易进行动态内省。 我认为最近在 PL/PgSQL 中进行了一些更改，使其更好地与动态 RECORDs 协作，其中列名等直到运行时才知道，但我不记得详细信息，请查阅文档。 - Craig Ringer