CODEWITHSUNDEEP
postgresqlplpgsql
bbozo
bbozo

Reputation: 7311

How can a PLSQL function return a result of an UPDATED...RETURNING query?

I have a function which needs to:

  1. call another function
  2. and then return the result of an update statement
  3. not fail at atomicity

The function looks something like this:

CREATE OR REPLACE FUNCTION bal_update_balances(_acct Accounts, _amount INT, _currency INT)
  RETURNS SETOF Balances AS $$
DECLARE
  --   rv SETOF Balances;
BEGIN
  -- STEP 0: initialize balances (make sure they are present in the DB)
  PERFORM bal_initialize_balances(_acct, _currency);

  -- STEP 1: update & return balances
  RETURN QUERY (
    UPDATE Balances
    SET amount   = amount + _amount,
    updated_at = now()
    WHERE id IN (SELECT id
    FROM bal_get_balances(_acct, _currency))
    RETURNING *
  );
END;
$$ LANGUAGE plpgsql;

And so far I have failed thusly:

  1. if I try to put the output of the UPDATE..RETURNING in a variable (rv) and then return that variable:
    1. the declaration of the variable fails (rv SETOF Balances fails with invalid type name "SETOF Balances")
    2. and then how to put the output of the UPDATE..RETURNING into rv is also a good question
  2. if I try do do a RETURN QUERY then it fails with a syntax error at or near "UPDATE"
  3. if I try to wrap the update statement into a select statement(SELECT * FROM (UPDATE ... RETURNING)) then the compiler complains about the SET part of the nested update statement (syntax error at or near "SET")
  4. if I rewrite the whole thing to iterate over bal_get_balances(_acct, _currency)) output (a SETOF Balances) and do RETURN NEXT then again the problem 1-2 arises, how do I put the output of UPDATE..RETURNING into a psql variable?

The general idea is that I can run this function from the main software (Ruby, but irrelevant) from within a transaction as a simulation of sorts and then depending on some kind of complex business logic in the main app decide whether to go through with the transaction, or throw it away depending on the exact state of the balances - it's so far the only way I thought of to combine complex and very dynamic app business logic with ACID requirements.

Upvotes: 1

Views: 86

Answers (3)

Vao Tsun
Vao Tsun

Reputation: 51599

just wrap it with CTE, like here:

t=# CREATE OR REPLACE FUNCTION s161()
  RETURNS SETOF s151 AS $$
DECLARE
BEGIN
  RETURN QUERY (
    with u as (UPDATE s151
    SET t= t||'***'
    RETURNING *
    )
    select * from u
  );
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
Time: 1.109 ms
t=# begin;
BEGIN
Time: 0.117 ms
t=# select * from s161() limit 3;
       t
---------------
 s141***
 events***
 tg_rep_que***
(3 rows)

Upvotes: 1

Abelisto
Abelisto

Reputation: 15624

create table t(i serial primary key, x int);
insert into t(x) select random()*10 from generate_series(1,10);

create function f() returns setof t language plpgsql as $$
declare
  r t[];
begin
  -- How to get result into the variable and return it
  with a as (update t set x = x*2 where i > 5 returning *)
    select array_agg(a.*) into r from a;
  return query select * from unnest(r);

  -- How to return result of update (just remove parenthesizes around it)
  return query
    update t set x = x*2 where i > 5 returning *;
end
$$;

select * from f();

http://rextester.com/OUZM15941

Upvotes: 1

klin
klin

Reputation: 121764

Use a variable of type record in a loop:

declare
    r record;
begin
    -- ...
    for r in 
        update balances
        set updated_at = now()
        -- ...
        returning *
    loop
        return next r;
    end loop;

Upvotes: 1

Related Questions