Return row from upsert method

Question

I have an upsert function that I modified from the documentation. However I have been trying to return the updated or inserted row. I'm calling this function from a node application and I need to keep track of which record has either been updated or inserted especially during sync script.

Here is the function:

create or replace function upsert_test(d TEXT, sys INT, val INT, p INT, inter BOOLEAN)
returns void as $$
begin
    update test_table set description=d, code=sys, val_id=val, provider_id=p, connect=inter where code=sys and val_id=val and provider_id=p;
    if found then
        return;
    end if;
    begin
        insert into test_table (description, code, val_id, provider_id, connect) values (d, sys, val, p, inter);
        return;
    exception when unique_violation then
    end;
    return;
end;
$$ language plpgsql;

I have tried to change the return type and have the function return a record but I can't seem to get it working.

score 2 · Accepted Answer · edited May 23 '17 at 10:26

Use the RETURNING clause. You can combine it with RETURN QUERY ...

CREATE OR REPLACE FUNCTION upsert_t2(d text, sys int, val int, p int, inter bool)
  RETURNS SETOF test_table AS
$func$
BEGIN
   RETURN QUERY
   UPDATE test_table t
   SET    description = d
         ,code        = sys
         ,val_id      = val
         ,provider_id = p
         ,connect     = inter
   WHERE  t.code        = sys
   AND    t.val_id      = val
   AND    t.provider_id = p
   RETURNING t.*;

   IF FOUND THEN
      RETURN;
   END IF;

   BEGIN
      RETURN QUERY
      INSERT INTO test_table (description, code, val_id, provider_id, connect)
      VALUES (d, sys, val, p, inter)
      RETURNING *;

   EXCEPTION WHEN UNIQUE_VIOLATION THEN
   END;

   RETURN;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM upsert_t2(...)

Reply to comment

I would try to avoid updates completely that do not change anything. Also, I would look to a data-modifying CTE in a loop:

CREATE OR REPLACE FUNCTION upsert_cte(d text, sys int, val int, p int
                                                              , inter bool)
  RETURNS SETOF test_table AS
$func$
BEGIN

LOOP
   BEGIN

   RETURN QUERY
   WITH sel AS (
      SELECT t.pk_col          -- primary key column
      FROM   test_table t
      WHERE  t.code        = sys
      AND    t.val_id      = val
      AND    t.provider_id = p
      FOR    SHARE             -- lock
      )
   , ins AS (
      INSERT INTO test_table (description, code, val_id, provider_id, connect)
      SELECT d, sys, val, p, inter
      WHERE  NOT EXISTS (SELECT 1 FROM sel)   -- if not found
      RETURNING *
      )
   , upd AS (
      UPDATE test_table t
      SET    description = d
            ,code        = sys
            ,val_id      = val
            ,provider_id = p
            ,connect     = inter
      FROM   sel
      WHERE  sel.pk_col = t.pk_col            -- if found (possibly mult. rows)
      AND    t.description IS DISTINCT FROM d
            ,t.code        IS DISTINCT FROM sys
            ,t.val_id      IS DISTINCT FROM val
            ,t.provider_id IS DISTINCT FROM p
            ,t.connect     IS DISTINCT FROM inter -- only if anything changes
      RETURNING t.*
      )
   SELECT * FROM ins
   UNION ALL
   SELECT * FROM upd;

   RETURN;                                  -- No error occurred, exit loop

   EXCEPTION WHEN UNIQUE_VIOLATION THEN     -- inserted in concurrent session
      RAISE NOTICE 'It happened!';       -- hardly ever happens, keep looping
   END;
END LOOP;

END
$func$ LANGUAGE plpgsql;

Explanation and links in this related answer:
Is SELECT or INSERT in a function prone to race conditions?

Hey, thanks for the reply. I updated my function and it is returning the query now. The only concern I have is that if the record I am updating/inserting exists and no updates were made I'd like to returning nothing. I thought the if found then return would handle that but It doesn't seem to be the case. I will be using this function in a sync script and I'm trying to keep track of the nightly updates/inserts. — Ptrkcon, Jun 04 '14 at 01:23
Thanks again. I'll have to tweak this just a bit to get it working the way I expect. It is inserting new rows where I think it should update. I think I have to modify 'sel' a bit to accommodate what I expect from the function. — Ptrkcon, Jun 05 '14 at 01:23
How would I modify this to update the row only when values other than sys change? For example if I want to update the description where sys=123 I don't want a whole new row entered (what it is doing now). — Ptrkcon, Jun 05 '14 at 17:51

Return row from upsert method

1 Answers1

Reply to comment