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

[Trenton]

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.

[courtlanda]

You cannot use the on conflict keyword to influence other tables. As stated by the https://www.postgresql.org/docs/current/sql-insert.html , 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.