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

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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 Reply