本文介绍了表名作为 PostgreSQL 函数参数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想在 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) or format(%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 函数参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

06-24 21:42