PostgreSql How to update several tables with one command?

Databases
1

Hello all,

I squeak by with my SQL programming skills, so please forgive me if I use incorrect terminology and my ignorance to SQL.

Right now, I have several tables that get updated. I am doing each update manually, in code, rather than have PostgreSQL update all of the tables by itself. I do not know what it is called when PostgreSQL does an insert or update to multiple tables at once (or seemingly at once) and with only one command from the code. Can anyone tell me what this is called, where I can find educational info on this and/or how to do a multi-table update without having to do each table in code separately.

The main reason NOT to do it separately, is for speed. Although very fast, it would be faster and relieve some work on the main application.

Thank you for your help!
Tim

2

I don’t use PostgreSQL myself, but I understand it does support Stored Procedures.

A Stored Procedure allows you to include and execute multiple CRUD operations and other types of function calls, all enclosed within a single “custom” procedure that you call once.

I hope that helps.

3

Thanks for the direction Scott!
Tim

4

You can use a DO block and construct SQL on the fly.

5

I would wrap all the updates in a transaction block. Then they would all commit at the same time. You could also rollback if there is a problem with any of the updates in the block.

6

Curious what kind of update this is?

7

just construct the string of all updates, insert into one string.
separate the commands with “;”
add a begin transaction at the beginning of the string and of course an end transaction at the end
send the whole string at once using db.sqlexecute( the-string-with-all-commands)
this way you don"t have to wait between all the commands in your app.

8

That’s not necessary with Postgresql:
See
https://www.postgresql.org/docs/11/protocol-flow.html
53.2.2.1. Multiple Statements In A Simple Query
When a simple Query message contains more than one SQL statement (separated by semicolons), those statements are executed as a single transaction, unless explicit transaction control commands are included to force a different behavior.

9

A rule is a option too, especially if a view makes sense in this context. https://www.postgresql.org/docs/12/sql-createrule.html

10

Hello all
@Kem - this is for inserts, basically a log file for activity by users.

I have a general transaction table, and also several specialty transaction tables. So when an action occurs, an insert occurs on at least 2 tables. Right now I have two separate commands, one for each table to be inserted. I thought I could offload one of the inserts to PostgreSQL so it could perform the update of the other transaction table on its own, automatically, without the app performing the extra work.

In reality, not sure how much time that would save but thought it worth the extra effort.
Tim

11

[quote=492073:@Tim Seyfarth]Hello all
@Kem - this is for inserts, basically a log file for activity by users.

I have a general transaction table, and also several specialty transaction tables. So when an action occurs, an insert occurs on at least 2 tables. Right now I have two separate commands, one for each table to be inserted. I thought I could offload one of the inserts to PostgreSQL so it could perform the update of the other transaction table on its own, automatically, without the app performing the extra work.

In reality, not sure how much time that would save but thought it worth the extra effort.
Tim[/quote]
You want a rule or a trigger. The trigger is the more common way to do this.

12

Thanks Thom

I’ll research this and give it a try.

Thanks to all of you!
Tim

13

FYI, we wrote a general trigger that can be applied to any table that will record to a log table the changing table’s schema, name, record id, type of change (insert, etc.), the query that caused the change, the old values before the change and new values after change (those last two as json), and timestamp. Postgres made this pretty easy actually.

14

I should mention that this is all automatic so we can apply it to any table at any time. The only requirement is that it has a single primary key column.

15

Where can I get the code for that Kem? I’d like to either use it as is, or as a basis for learning…
Tim

16 
CREATE OR REPLACE FUNCTION public.record_change_log_create()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
declare
  do_log boolean;
  old_json jsonb;
  new_json jsonb;
begin
  select g.is_record_change_log_enabled() into do_log;

  if TG_OP = 'INSERT' then
    if not do_log then
      return new;
    end if;

    insert into public.record_change_log (
      table_schema,
      table_name,
      table_id,
      user_id,
      action_type,
      new_values,
      query
    ) values (
      TG_TABLE_SCHEMA::text,
      TG_TABLE_NAME::text,
      new.id,
      public.user_session_id(),
      'INSERT',
      public.hstore_to_jsonb(public.hstore(new.*)),
      current_query());
    return new;

  elsif TG_OP = 'UPDATE' then
    if not do_log then
      return new;
    end if;

    old_json := public.hstore_to_jsonb(public.hstore(old.*) - public.hstore(new.*));

    if
      (old_json - array['updated_date', 'update_ts', 'updated_at', 'updated_on', 'updated_ts', 'update_user_id', 'updated_by', 'updated_by_id', 'updated_user_id']) <> '{}'::jsonb
    then
      new_json := public.hstore_to_jsonb(public.hstore(new.*) - public.hstore(old.*));

      insert into public.record_change_log (
        table_schema,
        table_name,
        table_id,
        user_id,
        action_type,
        old_values,
        new_values,
        query
      ) values (
        TG_TABLE_SCHEMA::text,
        TG_TABLE_NAME::text,
        new.id,
        public.user_session_id(),
        'UPDATE',
        old_json,
        new_json,
        current_query()
      );
    end if;
    return new;

  elsif TG_OP = 'DELETE' then
    if not do_log then
      return old;
    end if;

    insert into public.record_change_log (
      table_schema,
      table_name,
      table_id,
      user_id,
      action_type,
      old_values,
      query
    ) values (
      TG_TABLE_SCHEMA::text,
      TG_TABLE_NAME::text,
      old.id,
      public.user_session_id(),
      'DELETE',
      public.hstore_to_jsonb(public.hstore(old.*)),
      current_query());
    return old;
  end if;
end $function$
17

Thank you Kem!
Tim

18

I (and many many other PG developers) use the PGAudit extension for this very purpose, see: https://www.pgaudit.org/ . But it more or less does the same thing as Kem’s code.