Error when trying to return from plpgsql function using RETURNING with update/insert

Question

I am able to create the following function in my Postgresql 9.3 database:

create or replace function upsert_expense(
        p_id integer,
        p_amount integer,
        p_payer_id integer,
        p_category category_t,
        p_description text)
    returns text as
$$
begin
    if p_id = 0 then
        insert into expenses(amount, payer_id, category, description)
            values (p_amount, p_payer_id, p_category, p_description)
                returning row_to_json(expenses.*);
    end if;
    update expenses set
            amount=p_amount,
            payer_id=p_payer_id,
            category=p_category,
            description=p_description
        where id=p_id
            returning row_to_json(expenses.*);
end;
$$
language plpgsql;

The problem occurs when I try and call the function:

select upsert_expense(1,1,1,'groceries','description');

I get the following error:

ERROR:  query has no destination for result data

I think that this is due to improper use of the "returning" keyword, I can probably get the same result by using "pg_get_serial_sequence", but I feel that using "returning" would be more elegant.

Does anyone have any ideas? Or am I barking up the wrong tree here.

Just to paraphrase the error message, you're asking the INSERT to return data, but without giving any variable to return into. Either you need that data and then assign it to a variable, or you don't need it and then remove the `returning` clause. — Daniel Vérité, Nov 20 '13 at 00:22

score 4 · Accepted Answer · edited May 23 '17 at 12:00

It could work like this:

CREATE OR REPLACE FUNCTION upsert_expense(
       p_id integer
      ,p_amount integer
      ,p_payer_id integer
      ,p_category category_t
      ,p_description text
      ,OUT p_expenses json)
-- RETURNS json  -- not needed, since we use OUT parameter
AS 
$func$
BEGIN
IF p_id = 0 THEN
   INSERT INTO expenses(amount, payer_id, category, description)
   VALUES (p_amount, p_payer_id, p_category, p_description)
   RETURNING row_to_json(expenses.*)
   INTO   p_expenses;
ELSE
   UPDATE expenses
   SET    amount = p_amount
         ,payer_id = p_payer_id
         ,category = p_category
         ,description = p_description
   WHERE id = p_id
   RETURNING row_to_json(expenses.*)
   INTO   p_expenses;
END IF;

-- No RETURN needed since we use OUT parameter

END
$func$  LANGUAGE plpgsql;

Major points

The RETURNING clause in SQL INSERT or UPDATE statements does not return from the function. They just return a value from the SQL statement inside the function. Use the INTO clause to tell plpgsql where to write it to.
You could DECLARE a variable foo and use ... RETURNING INTO foo and later RETURN foo;. But since you just want to return the value from the function, take a shortcut and use an OUT parameter to begin with.
The function row_to_json() returns data type json, so the variable (or OUT parameter should be of matching type!
Since you are not actually returning from the function with the SQL RETURNING clause, you need to refit the IF logic, lest the UPDATE would be run every time.

Concurrency

Inserting and updating are often prone to race conditions in multi-user environments. You may want to employ a data-modifying CTE and possibly locks or more.

Data-modifying CTE

This does mostly the same as your function, except it decides whether to update or insert automatically based on the existence of a matching id, while your function is instructed explicitly with id = 0:

WITH values(id, amount, payer_id, category, description) AS (
   SELECT 1, 2, 3, 'some_val'::category_t, 'some text'  -- your values here
   )
, upd AS (
   UPDATE expenses e
   SET    amount = v.amount
         ,payer_id = v.payer_id
         ,category = v.category
         ,description = v.description
   FROM   values v
   WHERE  e.id = v.id
   RETURNING row_to_json(e.*)
   )
, ins AS (
   INSERT INTO expenses
         (amount, payer_id, category, description)
   SELECT amount, payer_id, category, description -- add id?
   FROM   values
   WHERE  NOT EXISTS (SELECT 1 FROM upd) -- no row found in update
   RETURNING row_to_json(expenses.*)
   )
SELECT * FROM upd
UNION ALL
SELECT * FROM ins; -- one row guaranteed

Minimizes the time slot for race conditions. You can wrap this into an SQL or plpgsql function, too. But there is more ... Consider these related questions:

Thanks for your reply, it is incredibly detailed. A couple of comments: As the driver I am using for the database access doesn't seem to support OUT parameters, it looks like I will have to use your suggestion of RETURNING INTO a variable and returning that. — ifross, Nov 20 '13 at 09:52
I am not sure I have to worry too much about concurrency in this particular case, but I was not aware of the solution you presented, so thanks! — ifross, Nov 20 '13 at 10:05
@ifross: You only need to run the create function script once. Execute raw SQL if your driver is not up to date. OUT parameters were introduced with Postgres 8.1, that's ages ago. — Erwin Brandstetter, Nov 20 '13 at 15:09

Error when trying to return from plpgsql function using RETURNING with update/insert

1 Answers1

Major points

Concurrency

Data-modifying CTE