问题描述
我想在Postgres函数中传递一个表名作为参数。我试过这段代码:
创建或替换函数some_f(参数字符变化)RETURNS integer
AS $$
BEGIN
IF EXISTS(select * from quote_ident($ 1)where quote_ident($ 1).id = 1)THEN
return 1;
END IF;
返回0;
END;
$$语言plpgsql;
选择some_f('table_name');
我得到了这个:
错误:在或接近。的语法错误
LINE 4:...从quote_ident($ 1)选择*其中quote_ident($ 1).id = 1)...
^
******* ***错误**********
错误:在或附近出现语法错误。
以下是我在更改为 select * from quote_ident时得到的错误($ 1)tab where tab.id = 1
:
错误:列tab.id不存在
LINE 1:... T EXISTS(select * from quote_ident($ 1)tab where tab.id ...
可能 quote_ident($ 1)
可以工作,因为没有其中quote_ident($ 1).id = 1
部分我得到 1
,这意味着选择了某些东西。为什么第一个 quote_ident($ 1)
可以工作和第二个不同时?这又怎么能解决?
这可以进一步简化和改进:
创建或替换函数some_f(_tbl regclass,OUT结果整数)AS
$ func $
BEGIN
EXECUTE格式('SELECT(EXISTS(SELECT 1 FROM%s WHERE id = 1)):: int',_tbl)
INTO结果;
END
$ func $ LANGUAGE plpgsql;
调用(使用模式限定名称的示例 - 请参阅下文):
SELECT some_f('myschema。 MYTABLE'); - 会失败quote_ident()
或者:
SELECT some_f(''我很少见的表名'')
要点
-
使用
OUT
参数来简化功能。您可以直接选择动态SQL的结果并完成。不需要额外的变量和代码。 你想要的,我保留在我的查询中。有很多种方法可以做到这一点。 您似乎想要一个整数,所以我将 -
我使用对象标识符类型作为
_tbl 。这样做的一切或会做,但效果更好,因为:
-
..它也可以防止 SQL注入。
-
..如果表名无效/不存在/当前用户不可见,它立即失败并更优雅。
-
我仍然使用,因为它简化了语法(并演示了它的使用方式),但使用
%s
而不是%I
。对于简单的例子,我们可以直接连接:
EXECUTE'SELECT(EXISTS(SELECT 1 FROM'|| _tbl || 'WHERE id = 1)):: int'
-
无需表格限定
id
列。
format(%I)
会失败,因为它们无法解决歧义。 b
使用PostgreSQL 9.1进行测试。
format()
至少需要该版本。
-
boolean
结果来自 EXISTS()
到 integer
我将返回 。这就是为什么总是转义用户正确输入动态SQL:
I want to pass a table name as a parameter in a Postgres function. I tried this code:
CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer
AS $$
BEGIN
IF EXISTS (select * from quote_ident($1) where quote_ident($1).id=1) THEN
return 1;
END IF;
return 0;
END;
$$ LANGUAGE plpgsql;
select some_f('table_name');
And I got this:
ERROR: syntax error at or near "."
LINE 4: ...elect * from quote_ident($1) where quote_ident($1).id=1)...
^
********** Error **********
ERROR: syntax error at or near "."
And here is the error I got when changed to this select * from quote_ident($1) tab where tab.id=1
:
ERROR: column tab.id does not exist
LINE 1: ...T EXISTS (select * from quote_ident($1) tab where tab.id...
Probably, quote_ident($1)
works, because without the where quote_ident($1).id=1
part I get 1
, which means something is selected. Why may the first quote_ident($1)
work and the second one not at the same time? And how could this be solved?
This can be further simplified and improved:
CREATE OR REPLACE FUNCTION some_f(_tbl regclass, OUT result integer) AS
$func$
BEGIN
EXECUTE format('SELECT (EXISTS (SELECT 1 FROM %s WHERE id = 1))::int', _tbl)
INTO result;
END
$func$ LANGUAGE plpgsql;
Call (example with schema-qualified name - see below):
SELECT some_f('myschema.mytable'); -- would fail with quote_ident()
Or:
SELECT some_f('"my very uncommon table name"')
Major points
Use an
OUT
parameter to simplify the function. You can directly select the result of the dynamic SQL into it and be done. No need for additional variables and code.EXISTS
does exactly what you want, I kept it in my query. There are various ways to do this.You seem to want an integer back, so I cast the
boolean
result fromEXISTS()
tointeger
, which yields exactly what you had. I would return boolean instead.I use the object identifier type
regclass
as input type for_tbl
. That does everythingquote_ident(_tbl)
orformat('%I', _tbl)
would do, but better, because:.. it prevents SQL injection just as well.
.. it fails immediately and more gracefully if the table name is invalid / does not exist / is invisible to the current user.
.. it works with schema-qualified table names, where a plain
quote_ident(_tbl)
orformat(%I)
would fail because they cannot resolve the ambiguity.
I still use
format()
, because it simplifies the syntax (and to demonstrate how it's used), but with%s
instead of%I
. For the simple example we could as well just concatenate:EXECUTE 'SELECT (EXISTS (SELECT 1 FROM ' || _tbl || ' WHERE id = 1))::int'
No need to table-qualify the
id
column.
Tested with PostgreSQL 9.1. format()
requires at least that version.
Here's why you always escape user input for dynamic SQL properly:
SQL Fiddle demonstrating SQL injection
这篇关于表名称作为PostgreSQL函数参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!