PostgreSQL – SQL Function to Return True/False on Insert Success

insertpostgresql

I want to write a SQL function which returns true if a certain INSERT inserted a row and false if no row was inserted. In this use case, we know that at most one row can be inserted.

I came up with the following solution:

create function test() returns boolean as
$$
    with
    dummy as
    (
        insert into    ...
        select         ...
        from           ...
        where          ... -- this will limit to at most one row
        returning      true
    )
    select exists(select true from dummy);
$$
language sql volatile;

This seems to work fine, but it is rather clumsy and verbose, in my opinion. Is there something better possible?

I first wanted to write the following:

create function test() returns boolean as
$$
    insert into    ...
    select         ...
    from           ...
    where          ...
    returning      true;
$$
language sql volatile;

The problem now is that not false but null is returned when no row was inserted.

Best Answer

One way with little noise in the code:

create function test()
  returns boolean as
$$
   WITH ins AS (
      insert into    ...
      select         ...
      from           ...
      where          ...
      returning      true
      )
   SELECT true FROM ins
   UNION ALL SELECT false;
$$
language sql volatile;

The function, being declared as returns boolean (and not returns setof boolean) returns only the first row. If no INSERT happens, the first (and only) row delivers false.

Or, a shorter version of your first variant:

...
   WITH ins AS (
      insert into    ...
      )
   SELECT EXISTS(TABLE ins)
...

See:

Related (with alternatives):

Quoting the manual to clarify the behavior of SQL functions:

SQL functions execute an arbitrary list of SQL statements, returning the result of the last query in the list. In the simple (non-set) case, the first row of the last query's result will be returned. (Bear in mind that “the first row” of a multirow result is not well-defined unless you use ORDER BY.) If the last query happens to return no rows at all, the null value will be returned.

Bold emphasis mine.

Note: the above is a single query. And we don't need ORDER BY, since a plain UNION ALL without more operations in the outer query level simply appends the resulting row to the set in last position.

Related Question