SQL function which returns true/false depending on success of insert query 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.
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:


Is there a shortcut for SELECT * FROM?


Related (with alternatives):


How to return a value from a function if no value is found


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.