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;

这在运行时给我错误关系'tname'des不存在".我是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屋！