Is it possible to call a function using the “on conflict” event?

by

The question:

The on conflict used to implement a upsert behavior is a wonderful feature. I’m wondering if I were to find the RFC for the feature whether I might find a way to use the on conflict event to call a function, or use something like:

on conflict
do $$
insert into another table
$$

Otherwise, considering myself still new to postgresql, is there a way to “catch” the conflict to enable further intervention.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

You cannot use the on conflict keyword to influence other tables. As stated by the official documentation, the conflict_action may be one of the following only:

conflict_action is one of:

    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                    ( column_name [, ...] ) = ( sub-SELECT )
                  } [, ...]
              [ WHERE condition ]

However, this does not mean there are no workarounds (at least for the do nothing command). You may use a function with the combination of the very useful found keyword in PostgreSQL to emulate something similar.

create or replace function generic_insert(
    p_user_name text
)
  returns boolean
  language plpgsql
as
$$
begin
    insert into users (user_name) values (p_user_name) on conflict do nothing;

    if not found then <your code> end if;

    return found;
end;
$$;

The trick here is that the found keyword returns “number of rows affected” (of the last executed query). Since do nothing did not influence any rows, found returns false, hence you’d know through this that your insert caused the do nothing portion of the on conflict command to trigger. You may of course put whatever code or insert commands you’d like inside the if statement.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment