Postgres 动态查询功能 [英] Postgres Dynamic Query Function

问题描述

我需要创建一个函数来运行查询并返回结果，表名和列名是函数的参数.我目前有这个:

I need to create a function that will run a query and return the results with the table name and the column name being arugments given to the function. I currently have this:

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;

这在运行时给了我错误Relation 'tname' des not exist".我是为 Postgres 创建函数的新手，因此感谢您提供任何帮助.我觉得 return int 是错误的，但我不知道还有什么可以让它返回返回的行的所有列.谢谢！

This gives me the error "Relation 'tname' des not exist" when run. I'm new to function creating for Postgres, so any help is appreciated. I feel like the return int is wrong, but I don't know what else to put to make it return all columns for the rows returned. Thanks!

推荐答案

您不能使用变量代替这样的标识符.您需要使用动态查询来完成.它看起来像这样:

You cannot use a variable in place of an identifier like that. You need to do it with dynamic queries. It will look something like this:

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

如果你使用的是 PostgreSQL 9.1 或更高版本，你可以使用 format() 函数，这使得构造这个字符串变得更更容易.

If you are using PostgreSQL 9.1 or above, you can use the format() function which makes constructing this string much easier.

这篇关于Postgres 动态查询功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！