表名称作为PostgreSQL函数参数 [英] Table name as a PostgreSQL function parameter

问题描述

我想在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的结果并完成。不需要额外的变量和代码。

你想要的，我保留在我的查询中。有很多种方法可以做到这一点。 您似乎想要一个整数，所以我将 boolean 结果来自 EXISTS（）到 integer 我将返回 布尔 。


• 我使用对象标识符类型 regclass 作为 _tbl 。这样做的一切 quote_ident （_tbl） 或 format（'％I'，_tbl） 会做，但效果更好，因为：



  
  

  • ..它也可以防止 SQL注入。
    

  • ..如果表名无效/不存在/当前用户不可见，它立即失败并更优雅。

  format（％I）会失败，因为它们无法解决歧义。 b

  • 我仍然使用 format（） ，因为它简化了语法（并演示了它的使用方式），但使用 ％s 而不是％I 。对于简单的例子，我们可以直接连接：
    
    

      EXECUTE'SELECT（EXISTS（SELECT 1 FROM'|| _tbl || 'WHERE id = 1））:: int'
      

  
  

  • 无需表格限定 id 列。

  
  
  
  
  

  使用PostgreSQL 9.1进行测试。 format（）至少需要该版本。

  
  
  

  这就是为什么总是转义用户正确输入动态SQL：
  
  

  SQL小提琴演示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 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.

    • .. it works with schema-qualified table names, where a plain quote_ident(_tbl) or format(%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函数参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！