1

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.

4
  • How about COALESCE(test(), FALSE)? Feb 28, 2019 at 17:32
  • @dezso I would rather not solve this at the caller side of the function, as this is easily forgotten. Also, I would say that we should not have to expect a null from a function that returns a boolean.
    – Dave Teezo
    Feb 28, 2019 at 17:41
  • Is language plpgsql acceptable ?
    – Sahap Asci
    Feb 28, 2019 at 20:28
  • @SahapAsci Is it possible to do this cleaner with pure SQL?
    – Dave Teezo
    Mar 1, 2019 at 11:35

1 Answer 1

2

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.