postgresql - 在多个表中插入动态列列表的函数

Question

我想做something like this在 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 并希望未指定的字段使用表中的默认值。目前这是不可能的，因为要调用此函数我需要指定所有字段。

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

Answer 1

使用default values用于函数参数和 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[]);

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

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

如果需要一些非空值，则可能不适用于您的表，但可以证明一旦您提供了命名参数，您就可以省略任何具有默认值的参数。省略的参数采用声明的默认值(不要与列默认值混淆)。此相关答案中的更多信息:
Functions with variable number of input parameters

要点

• 利用 DEFAULT keyword of the INSERT command .使系统插入表的默认列。
  替代方案是仅列出 INSERT 中的列在 VALUES 中获取相应项目的行行。

• 您必须使用dynamic SQL and EXECUTE 操纵语句本身，而不仅仅是值。

• “摆动柱”是 field1至 field3和 language ，其余的根据定义是硬连线的。根据需要变化。

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

• 如果声明了所有涉及的列 NOT NULL - 尚未澄清 - 您可以简化:传递 NULL对于任何应该获得列默认值并调整 CASE 的列声明。

• 如果省略 _cols , 将插入所有字段。作为_cols是最后IN参数并具有默认值，您始终可以省略它。

• 我雇用了 USING clause for EXECUTE 将参数作为值传递，并防止使用动态构建的查询字符串进行 SQL 注入(inject)。

• 我雇用 format()简化语句汇编并避免多次赋值。在 PL/pgSQL 中更便宜。

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

• 您可以为 type 插入一个常量值在INSERT直接声明。这样您就不需要任何变量并保存额外的赋值。

• 已使用 PostgreSQL 9.1 进行测试，但应适用于 8.4 之后的所有版本 - 除了 format() 这是在 9.1 中引入的。