将表名和列名定义为plpgsql函数中的参数？ [英] Define table and column names as arguments in a plpgsql function?

问题描述

我想要一个简单的，但我正在开始进入Postgres函数的第一步，我找不到任何可行的工作。创建一个函数，将修改表和/或列，我找不到正确的方式来指定我的表和列作为我的函数中的参数。

Something例如：

  CREATE OR REPLACE FUNCTION foo（t table）
 RETURNS void AS $$ 
 BEGIN 
 alter table t add column c1 varchar（20）; 
 alter table t add column c2 varchar（20）; 
 alter table t添加列c3 varchar（20）; 
 alter table t添加列c4 varchar（20）; 
 END; 
 $$ LANGUAGE PLPGSQL; 
 
 select foo（some_table）
  

在另一种情况下，我想有一个函数可以改变某个表的某个列：

pre code创建或替换函数foo（t表，c列）
RETURNS void AS $$
BEGIN
UPDATE t SET c =这是一个测试;
END;
$$ LANGUAGE PLPGSQL;

是否可以这样做？

解决方案

您必须针对 SQL注入 每当你把用户输入代码。这包括来自系统目录或来自直接用户输入的表格和列名称。这样您也可以防止使用非标准标识符的简单例外。基本上有 三个 内置方法：

1。 format（）

第一个查询已清理完毕：

  CREATE OR REPLACE FUNCTION foo（_t text）
 RETURNS void AS 
 $ func $ 
 BEGIN 
 EXECUTE格式（'
 ALTER TABLE％I ADD COLUMN c1 varchar（20）
，ADD COLUMN c2 varchar（20）'，_t）; 
 END 
 $ func $ LANGUAGE plpgsql; 
  

format（）需要Postgres 9.1或更高版本。与 ％I 格式说明符一起使用它。

暧昧。您可能必须提供模式名称以避免意外更改错误的表格。相关：

• 在触发器函数中插入动态表名称


• search_path如何影响标识符解析和当前模式

另外：加入 多列带有一个 ALTER TABLE 命令便宜。

2。 regclass

您也可以使用强制类型转换为注册类（ regclass ）来处理 现有的 表名称。可选模式限定。这对于主叫用户无效且可见的表名称立即失败。第一个查询通过强制转换为 regclass ：

  CREATE OR REPLACE FUNCTION foo（_t regclass）
 RETURNS void AS 
 $ func $ 
 BEGIN 
 EXECUTE'ALTER TABLE'|| _t ||'ADD COLUMN c1 varchar（20）
，ADD COLUMN c2 varchar（20）'; 
 END 
 $ func $ LANGUAGE plpgsql; 
  

通话：

  SELECT foo（'table_name'）; 
  

或者：

  SELECT foo（'my_schema.table_name':: regclass）;除此之外：请考虑使用  
> > 
 
 
 /current/interactive/datatype-character.htmlrel =nofollow noreferrer>  text 而不是 varchar（20） 。
 
 
 
 3。   quote_ident（）  
 
 
 
第二个查询被清理： 
 
 
  CREATE OR REPLACE FUNCTION foo（_t regclass，_c text）
 RETURNS void AS 
 $ func $ 
 BEGIN 
 EXECUTE'UPDATE'|| _t ||' - 用regclass 
 SET'||进行消毒quote_ident（_c）||'=''这是一个测试'''; 
 END 
 $ func $ LANGUAGE plpgsql; 
  
对于多个连接/插值， format（）更清洁... 
 
 
 
相关答案： 
 
 
 
 
 表名作为PostgreSQL函数参数 
 
 
  Postgres功能与准备好的查询 
 
 
 
 
 
 
 
 
区分大小写！
 
 
 
意识到未加引号的标识符 在这里转换为小写字母。在SQL中用作标识符时 Postgres演员自动降至小写。但是我们在这里为动态SQL传递字符串。当按照演示转义时，通过双引号（UserS）保留CaMel-case标识符（如 UserS ），就像其他非标准名称，如name with space SELECT等。因此，在这种情况下名称是区分大小写的。
 
 
 
 我的建议是专门使用合法的小写字母标识符，而不用担心。  / p> 
 
 
另外：值为单引号，标识符为双引号。
 
It must be simple, but I'm making my first steps into Postgres functions and I can't find anything that works...

I'd like to create a function that will modify a table and / or column and I can't find the right way of specifying my tables and columns as arguments in my function.

Something like:
CREATE OR REPLACE FUNCTION foo(t table)
RETURNS void AS $$
BEGIN
   alter table t add column c1 varchar(20);
   alter table t add column c2 varchar(20);
   alter table t add column c3 varchar(20);
   alter table t add column c4 varchar(20);
END;
$$ LANGUAGE PLPGSQL;

select foo(some_table)
In another case, I'd like to have a function that alters a certain column from a certain table:
CREATE OR REPLACE FUNCTION foo(t table, c column)
RETURNS void AS $$
BEGIN
   UPDATE t SET c = "This is a test";
END;
$$ LANGUAGE PLPGSQL;
Is it possible to do that?
解决方案
You must defend against SQL injection whenever you turn user input into code. That includes table and column names coming from system catalogs or from direct user input alike. This way you also prevent trivial exceptions with non-standard identifiers. There are basically three built-in methods:

1. format()

1st query, sanitized:
CREATE OR REPLACE FUNCTION foo(_t text)
  RETURNS void AS
$func$
BEGIN
   EXECUTE format('
   ALTER TABLE %I ADD COLUMN c1 varchar(20)
                , ADD COLUMN c2 varchar(20)', _t);
END
$func$  LANGUAGE plpgsql;
format() requires Postgres 9.1 or later. Use it with the %I format specifier.

The table name alone may be ambiguous. You may have to provide the schema name to avoid changing the wrong table by accident. Related:


INSERT with dynamic table name in trigger function
How does the search_path influence identifier resolution and the "current schema"


Aside: adding multiple columns with a single ALTER TABLE command is cheaper.

2. regclass

You can also use a cast to a registered class (regclass) for the special case of existing table names. Optionally schema-qualified. This fails immediately and gracefully for table names that are not be valid and visible to the calling user. The 1st query sanitized with a cast to regclass:
CREATE OR REPLACE FUNCTION foo(_t regclass)
  RETURNS void AS
$func$
BEGIN
   EXECUTE 'ALTER TABLE '|| _t ||' ADD COLUMN c1 varchar(20)
                                 , ADD COLUMN c2 varchar(20)';
END
$func$  LANGUAGE plpgsql;
Call:
SELECT foo('table_name');
Or:
SELECT foo('my_schema.table_name'::regclass);
Aside: consider using just text instead of varchar(20).

3. quote_ident()

The 2nd query sanitized:
CREATE OR REPLACE FUNCTION foo(_t regclass, _c text)
  RETURNS void AS
$func$
BEGIN
   EXECUTE 'UPDATE '|| _t ||'   -- sanitized with regclass
            SET '|| quote_ident(_c) ||' = ''This is a test''';
END
$func$  LANGUAGE plpgsql;
For multiple concatenations / interpolations, format() is cleaner ...

Related answers:  


Table name as a PostgreSQL function parameter  
Postgres functions vs prepared queries




Case sensitive!

Be aware that unquoted identifiers are not cast to lower case here. When used as identifier in SQL Postgres casts to lower case automatically. But here we pass strings for dynamic SQL. When escaped as demonstrated, CaMel-case identifiers (like UserS) will be preserved by doublequoting ("UserS"), just like other non-standard names like "name with space" "SELECT"etc. Hence, names are case sensitive in this context.

My standing advice is to use legal lower case identifiers exclusively and never worry about that.

Aside: single quotes for values, double quotes for identifiers.

                        
这篇关于将表名和列名定义为plpgsql函数中的参数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！