INSERT select rows using pl/pgsql

Question

Question: Is there a way in (maybe pl/pgsql function way?) to create an INSERT/UPDATE/DELETE query on a specific table using the values found in my log table (namely the values 'action', 'schema_name', 'table_name', 'column_name', 'data_type' (i.e. the column data type), and 'new_val'?).

The table being logged and the table that I need to run the INSERT/UPDATE/ or DELETE on looks like this:

..and the log table looks like this:

...the 4 highlighted log entries should be INSERT'ed into the table like this:

... I'm trying to find a way to run a INSERT/UPDATE/or DELETE on ANOTHER DATABASE table (which is identical in names/schema/etc to the table being logged) after a selection of the specific 'usr' and 'event_date' in the logging table.

To just get the results I want (for the INSERT statement only - see below) the SQL is pretty knarly (demo in SQL FIDDLE). I'm pretty interested in finding out if another way is possible....

INSERT INTO Engineering.Elective_Courses 
            (gid, grade, class, student_id) 
    WITH
    t1 AS
    (Select new_val 
    From student.history
    WHERE
       column_name = 'gid'
    AND
       usr = 'Principal K.'
    AND
       (event_date >= '2017-01-26' AND event_date <  '2017-01-29')),
    t2 AS (Select new_val 
    From student.history
    WHERE
       column_name = 'grade'
    AND
       usr = 'Principal K.'
    AND
       (event_date >= '2017-01-26' AND event_date <  '2017-01-29')),
    t3 AS (Select new_val 
    From student.history
    WHERE
       column_name = 'class'
    AND
       usr = 'Principal K.'
    AND
       (event_date >= '2017-01-26' AND event_date <  '2017-01-29')),
    t4 AS (Select new_val 
    From student.history
    WHERE
       column_name = 'student_id'
    AND
       usr = 'Principal K.'
    AND
       (event_date >= '2017-01-26' AND event_date <  '2017-01-29'))
     select t1.new_val::int, t2.new_val, t3.new_val, t4.new_val::int
      from t1,t2, t3, t4;

Answer 1

You have to use dynamic SQL.

This query aggregates data for individual actions:

select 
    action, event_date, usr, 
    schema_name, table_name, pkey_id,
    string_agg(quote_ident(column_name), ',' order by history_id) as cols, 
--  string_agg(quote_literal(new_val), ',' order by history_id) as vals
--  correction:
    string_agg(coalesce(quote_literal(new_val), 'null'), ',' order by history_id) as vals
from student.history
group by 1, 2, 3, 4, 5, 6;

 action |     event_date      |     usr      | schema_name |    table_name    | pkey_id |            cols            |            vals            
--------+---------------------+--------------+-------------+------------------+---------+----------------------------+----------------------------
 DELETE | 2017-01-28 12:20:03 | Ast. Dean J. | Engineering | Elective_Courses | 14      | grade                      | 
 INSERT | 2017-01-26 22:42:53 | Principal K. | Engineering | Elective_Courses | 12      | gid,grade,class,student_id | '12','B-','PYS7C','607752'
 UPDATE | 2017-01-26 22:42:53 | Ast. Dean J. | Engineering | Elective_Courses | 13      | grade                      | 'C'
(3 rows)

For better safety the history table should have an additional unique id of action to distinguish between e.g. two insertions on the same table by the same user at the same time, though such a coincidence is unlikely.

The function is based on the above query. It has one argument, text of WHERE clause on the history table. It returns generated queries. It can also execute the queries (in the commented piece of code).

create or replace function restore_log(condition text)
returns setof text language plpgsql as $$
declare
    relid regclass;
    pkey text;
    query text;
    rec record;
begin
    for rec in
        execute format('
            select 
                action, event_date, usr, 
                schema_name, table_name, pkey_id,
                string_agg(quote_ident(column_name), '','' order by history_id) as cols, 
                string_agg(coalesce(quote_literal(new_val), ''null''), '','' order by history_id) as vals
            from student.history
            where %s
            group by 1, 2, 3, 4, 5, 6',
            condition)
    loop
        relid:= format('%s.%s', rec.schema_name, rec.table_name)::regclass;
        pkey:= get_pkey_name(relid); -- see below
        query:= case rec.action
            when 'INSERT' then
                format(
                    'insert into %s.%s (%s) values (%s)',
                    rec.schema_name, rec.table_name, rec.cols, rec.vals)
            when 'UPDATE' then
                format(
                    'update %s.%s set (%s) = (%s) where %s = %s',
                    rec.schema_name, rec.table_name, rec.cols, rec.vals, pkey, rec.pkey_id)
            when 'DELETE' then
                format(
                    'delete from %s.%s where %s = %s',
                    rec.schema_name, rec.table_name, pkey, rec.pkey_id)
            else null end;
        return next query;
--      if query not null then
--          execute(query);
--      end if;
    end loop;
end $$;

Usage examples:

select * from restore_log('true');

                                                restore_log                                                
-----------------------------------------------------------------------------------------------------------
 delete from Engineering.Elective_Courses where gid = 14
 insert into Engineering.Elective_Courses (gid,grade,class,student_id) values ('12','B-','PYS7C','607752')
 update Engineering.Elective_Courses set (grade) = ('C') where gid = 13
(3 rows)


select * from restore_log($$
    usr = 'Principal K.' 
    and event_date >= '2017-01-26' 
    and event_date <  '2017-01-29'$$);

                                                restore_log                                                
-----------------------------------------------------------------------------------------------------------
 insert into Engineering.Elective_Courses (gid,grade,class,student_id) values ('12','B-','PYS7C','607752')
(1 row)     

The function which finds column name of single-column primary key of a given table (used in restore_log()):

create or replace function get_pkey_name(regclass)
returns name language sql as $$
    select attname
    from pg_constraint c
    join pg_attribute a on attrelid = conrelid and attnum = conkey[1]
    where conrelid = $1
    and contype = 'p'
    and cardinality(conkey) = 1
$$;

---

Safety notice, basically you should use format('%I.%I, schema_name, table_name) for security reasons, but in this case it would give wrong results due to the use of capital letters in the data.

Answer 2

For more than one row to insert, the cross joins in your displayed query would produce an incorrect Cartesian product. Never use this.

If the target table is predefined, a plain crosstab() query does the job:

INSERT INTO engineering.elective_courses (gid, grade, class, student_id)

SELECT * FROM crosstab(
     $$SELECT pkey_id, column_name, new_val 
       FROM   student.history
       WHERE  usr = 'Principal K.'       -- your criteria here
       AND    event_date >= '2017-01-26'
       AND    event_date <  '2017-01-29'
       AND    action = 'INSERT'
       ORDER  BY 1$$
   , $$SELECT unnest('{grade,class,student_id}'::text[])$$)
   AS ct (gid int, grade varchar, class varchar, student_id int);

The extra row for gid (history_id = 0 in the example) must match the column pkey_id and is completely redundant. crosstab() simply ignores it, since 'gid' is not listed as target column in the second function parameter.

Detailed explanation:

• PostgreSQL Crosstab Query

---

You can create the statement dynamically, too, if the target table should not be predefined. Closely related answer:

• Merge a table and a change log into a view in PostgreSQL

Advanced:

• Dynamic alternative to pivot with CASE and GROUP BY