Function to INSERT dynamic list of columns in multiple tables

Question

I want to do something like this in PostgreSQL.

I tried this:

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;

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.

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?

Answer 1

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

Demo function

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;

Call

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

Major points

• 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.

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

• 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.

• 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.

• 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.

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

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

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

• 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.

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