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;

You could create a pl/pgsql using the INFORMATION_SCHEMA schema in Postgres (pg_catalog). But I doubt that it wouldn't be as knarly as your query. — Jorge Campos, Jan 28 '17 at 21:38
@McNets the third graphic is the expected result. The rows in yellow have been appended based on the information found in the 'log' table. — user14696, Jan 29 '17 at 23:09
So the schema of the target table is predefined, and entries in `student.history` always match? Also, your Postgres version, please. Are you free to install additional modules (from the standard distribution)? And please remember to post ***text*** for all data items, never images. — Erwin Brandstetter, Jan 30 '17 at 19:06

klin · Answer 1 · 2017-01-31T01:20:42.153

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.

I had to change some things (not on 9.4 so no cardinality()... I used this instead: `AND array_length(conkey, 1) = 1`). Right now I am getting a ` ERROR: INSERT has more target columns than expressions` on `...INSERT then format ()... else null end;` ... do you see this? — user14696, Jan 30 '17 at 21:03
Note ... it looks like it can't handle cases where there is a `NULL` value being INSERT'd. Ex: — user14696, Jan 30 '17 at 21:11
Right, `null` values need `coalesce()`, see the edited answer. — klin, Jan 30 '17 at 21:48
Yes, you can use `perform dblink_exec(query)` for insert/update/delete. — klin, Jan 30 '17 at 23:22
hm... I tried to swap `execute(query)` w / `PERFORM dblink_exec(`'dbname=ex'', query)' and got a `Context: Error occurred on dblink connection named "unnamed": could not execute command.`. When I use the connection strings outside of the function I do get a 'Ok' — user14696, Jan 31 '17 at 00:49
`perform dblink_exec()` inside the function works well for me. But I've found an oversight in `UPDATE` query (lack of `SET`). The code corrected, please check it. — klin, Jan 31 '17 at 01:21
I did notice one weird fact. `select * from restore_log('true')` doesn't work after I do a `DELETE` then an `UPDATE`. Gets stuck just repeating the `DELETE`. But with the specific `WHERE` conditions it does work. — user14696, Feb 02 '17 at 01:45
It's rather not related to the function which simply generates consecutive queries. I suppose the automatic generation of queries in conjunction with a remote access by dblink might be difficult to debug. — klin, Feb 02 '17 at 02:52

score 1 · Answer 2 · edited May 23 '17 at 12:16

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

INSERT select rows using pl/pgsql

2 Answers2