在多个表中插入动态列列表的功能 [英] Function to INSERT dynamic list of columns in multiple tables

问题描述

我想在PostgreSQL中做类似的事情.

I want to do something like this in PostgreSQL.

我尝试过:

CREATE or replace FUNCTION create_patient(_name text, _email text, _phone text
     , _password text
     , _field1 text, _field2 text, _field3 timestamp, _field4 text
     , OUT _pid integer, OUT _id integer
    ) RETURNS record AS
$$
DECLARE
    _id integer;
    _type text;
    _pid integer;
BEGIN
    _type := 'patient';

    INSERT into patients (name, email, phone, field1, field2, field3)
    VALUES (_name, _email, _phone, _field1, _field2, _field3)
    RETURNING id into _pid;

    INSERT into users (username, password, type, pid, phone, language)
    VALUES (_email, _password, _type, _pid, _phone, _field4)
    RETURNING id into _id;
END;
$$ LANGUAGE plpgsql;

但是在很多情况下，我不想指定某些 field1/field2/field3/field4 ，并且希望未指定的字段使用表中的默认值.当前这是不可能的，因为要调用此函数，我需要指定所有字段.

But there are a lot of instances where I would not want to specify some of field1 / field2 / field3 / field4 and want the unspecified fields to use the default value in the table. Currently that is not possible, because to call this function I need to specify all fields.

是否有一种简单的方法可以在PL/pgSQL中为 INSERT 创建包装过程，在其中可以指定要插入的字段?

Is there a simple way to create a wrapper procedure for INSERT in PL/pgSQL where I can specify which fields I want to insert?

推荐答案

使用 默认值 表示函数参数，并 动态SQL ...

Use default values for function parameters and dynamic SQL ...

CREATE OR REPLACE FUNCTION create_patient(
     _name     text      = NULL  -- always updated, NULL if not provided
    ,_email    text      = NULL
    ,_phone    text      = NULL
    ,_password text      = NULL
    ,_field1   text      = NULL  -- variable parameters
    ,_field2   text      = NULL
    ,_field3   timestamp = NULL
    ,_language text      = NULL
    ,_cols     text[]    = '{field1,field2,field3,language}'
    ,OUT _pid  text
    ,OUT _id   int)
  RETURNS record AS
$func$
BEGIN
   EXECUTE format(
   'INSERT INTO patients (field1, field2, field3, name, email, phone)
    VALUES               (%s,     %s,     %s,     $4,   $5,    $6   )
    RETURNING id'
       ,CASE WHEN 'field1' = ANY(_cols) THEN '$1' ELSE 'DEFAULT' END
       ,CASE WHEN 'field2' = ANY(_cols) THEN '$2' ELSE 'DEFAULT' END
       ,CASE WHEN 'field3' = ANY(_cols) THEN '$3' ELSE 'DEFAULT' END)
    INTO  _pid                       -- return value, also used in 2nd insert
    USING _field1, _field2, _field3, _name, _email, _phone;

   EXECUTE format(
   'INSERT INTO users (username, password, type,        pid, phone, language)
    VALUES            ($1,       $2,       $$patient$$, $3,  $4,    %s      )
    RETURNING id'
      ,CASE WHEN 'language' = ANY(_cols) THEN '$4' ELSE 'DEFAULT' END)
    INTO  _id                        -- return value
    USING _email, _password, _pid, _phone, _language;
END
$func$  LANGUAGE plpgsql;

致电

SELECT * FROM create_patient('myname','myemail','myphone','mypassword'
,'myfield1','myfield2',NULL,'English','{field2,language,field1}'::text[]);

由于该函数使用命名参数且每个参数都有一个默认值，因此您甚至可以这样调用它:

As the function uses named parameters and each has a default value, you can even call it like this:

SELECT * FROM create_patient(_name := 'myname');

如果需要某些非空值，则可能不适用于您的表，但是它证明了一旦提供命名参数，您就可以忽略带有默认值的任何参数.省略的参数采用声明的默认值(不要与列默认值混淆).此相关答案中的更多内容:
具有可变数量输入参数的函数

May not work for your tables if some non-null values are required, but goes to demonstrate that you can omit any parameters with defaults on them once you provide named parameters. Omitted parameters take the default value as declared (not to be confused with column defaults). More in this related answer:
Functions with variable number of input parameters

• 使用 DEFAULT INSERT 命令的code>关键字.它使系统将表的默认列插入.
  另一种选择是只列出 INSERT 行中的列，这些列在 VALUES 行中获得相应的项目.

• Make use of the DEFAULT keyword of the INSERT command. It makes the system insert the column default of the table.
  Alternative would be to only list columns in the INSERT line that get a corresponding item in the VALUES line.

您必须使用

You have to use dynamic SQL and EXECUTE to manipulate the statement itself, not just the values.

摇摆列"是 field1 到 field3 和 language ，其余部分按照定义进行硬接线.视需要而定.

"Swing columns" are field1 to field3 and language, the rest is hardwired as per definition. Vary as needed.

我的函数适用于所有情况，您甚至可以提供 NULL 值，而不是列的默认值.这就需要一个参数 _cols 来提供要插入哪些列的信息.

My function works for all cases, you can even provide a NULL value instead of the column default. That requires a parameter _cols providing the information which columns are to be be inserted.

如果所有涉及的列都被声明为 NOT NULL -尚未澄清-您可以简化:为应该获得默认列的任何列传递 NULL 并修改 CASE 语句.

If all involved columns were declared NOT NULL - which has not been clarified - you can simplify: pass NULL for any column that should get the column default and adapt the CASE statements.

如果省略 _cols ，将插入所有字段.由于 _cols 是最后一个 IN 参数，并且具有默认值，因此您可以始终忽略它.

If you omit _cols, all fields will be inserted. As _cols is the last IN parameter and has a default value, you can always omit it.

我雇用了 EXECUTE 的> USING 子句将参数作为 values 传递，并防止使用动态构建的查询字符串进行SQL注入.

I employ the USING clause for EXECUTE to pass parameters as values and prevent SQL injection with dynamically built query strings.

我使用 format()来简化语句的汇编并避免多次分配.在PL/pgSQL中更便宜.

I employ format() to simplify statement assembly and avoid multiple assignments. Cheaper in PL/pgSQL.

在函数体中不要 DECLARE _id 和 _pid ，因为它们是由 OUT声明的参数并自动返回.

Don't DECLARE _id and _pid in the function body, since they are declared by OUT parameters in the header and returned automatically.

您可以直接在 INSERT 语句中为 type 插入常量值.这样，您就不需要任何变量并保存其他分配.

You can insert a constant value for type in the INSERT statement directly. This way you don't need any variables and save additional assignments.

已通过PostgreSQL 9.1 进行了测试，但应该适用于8.4以后的所有版本-除

Tested with PostgreSQL 9.1, but should work with all versions since 8.4 - except for format() which was introduced with 9.1.

这篇关于在多个表中插入动态列列表的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！