问题描述
我想在 Postgres 函数中传递一个表名作为参数.我试过这个代码:
CREATE OR REPLACE FUNCTION some_f(param character variables) 返回整数作为 $$开始IF EXISTS (select * from quote_ident($1) where quote_ident($1).id=1) THEN返回 1;万一;返回0;结尾;$$ 语言 plpgsql;选择 some_f('table_name');
我得到了这个:
错误:."处或附近的语法错误第 4 行:...elect * from quote_ident($1) where quote_ident($1).id=1)...^********** 错误 **********错误:."处或附近的语法错误
这是我更改为这个 select * from quote_ident($1) tab where tab.id=1
时得到的错误:
错误:列 tab.id 不存在第 1 行:...T EXISTS(从 quote_ident($1) 选项卡中选择 *,其中 tab.id...
可能,quote_ident($1)
有效,因为没有 where quote_ident($1).id=1
部分我得到 1
,其中意味着选择了某些东西.为什么第一个 quote_ident($1)
可以工作而第二个不能同时工作?如何解决这个问题?
这可以进一步简化和改进:
CREATE OR REPLACE FUNCTION some_f(_tbl regclass, OUT result integer)语言 plpgsql AS$func$开始EXECUTE 格式('SELECT (EXISTS (SELECT FROM %s WHERE id = 1))::int', _tbl)INTO结果;结尾$func$;
使用模式限定名称调用(见下文):
SELECT some_f('myschema.mytable');-- 使用 quote_ident() 会失败
或者:
SELECT some_f('"我非常不常见的表名"');
要点
使用OUT
参数来简化函数.可以直接把动态SQL的结果选进去就大功告成了.无需额外的变量和代码.
EXISTS
完全符合您的要求.如果该行存在,您将获得 true
,否则将获得 false
.有多种方法可以做到这一点,EXISTS
通常是最有效的.
你似乎想要返回一个 integer,所以我将 boolean
结果从 EXISTS
转换为 integer
,这正是你所拥有的.我会返回 boolean.>
我使用对象标识符类型 regclass
作为 _tbl
的输入类型.quote_ident(_tbl)
或 format('%I', _tbl)
会做,但更好,因为:
.. 它还可以防止SQL 注入.
.. 如果表名无效/不存在/当前用户不可见,它会立即失败并更优雅.(
regclass
参数仅适用于现有表.).. 它适用于模式限定的表名,其中纯
quote_ident(_tbl)
或format(%I)
会失败,因为它们无法解析歧义.您必须分别传递和转义架构和表名.
它显然只适用于现有表.
我仍然使用 格式()
,因为它简化了语法(并演示了如何使用),但使用 %s
而不是 %I
>.通常,查询更复杂,因此 format()
有更多帮助.对于简单的例子,我们也可以连接:
EXECUTE 'SELECT (EXISTS (SELECT FROM ' || _tbl || ' WHERE id = 1))::int'
无需对 id
列进行表限定,而 FROM
列表中只有一个表.在这个例子中不可能有歧义.EXECUTE
中的(动态)SQL 命令具有单独的作用域,函数变量或参数在那里不可见 - 与函数体中的普通 SQL 命令相反.
这就是您总是正确转义动态 SQL 的用户输入的原因:
db<>fiddle 此处演示 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)
LANGUAGE plpgsql AS
$func$
BEGIN
EXECUTE format('SELECT (EXISTS (SELECT FROM %s WHERE id = 1))::int', _tbl)
INTO result;
END
$func$;
Call 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. You get true
if the row exists or false
otherwise. There are various ways to do this, EXISTS
is typically most efficient.
You seem to want an integer back, so I cast the boolean
result from EXISTS
to integer
, 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 everything quote_ident(_tbl)
or format('%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. (A
regclass
parameter is only applicable for existing tables.).. it works with schema-qualified table names, where a plain
quote_ident(_tbl)
orformat(%I)
would fail because they cannot resolve the ambiguity. You would have to pass and escape schema and table names separately.
It only works for existing tables, obviously.
I still use format()
, because it simplifies the syntax (and to demonstrate how it's used), but with %s
instead of %I
. Typically, queries are more complex so format()
helps more. For the simple example we could as well just concatenate:
EXECUTE 'SELECT (EXISTS (SELECT FROM ' || _tbl || ' WHERE id = 1))::int'
No need to table-qualify the id
column while there is only a single table in the FROM
list. No ambiguity possible in this example. (Dynamic) SQL commands inside EXECUTE
have a separate scope, function variables or parameters are not visible there - as opposed to plain SQL commands in the function body.
Here's why you always escape user input for dynamic SQL properly:
db<>fiddle here demonstrating SQL injection
这篇关于表名作为 PostgreSQL 函数参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!