How to use variables in PostgreSQL transaction

user_12020884 0
Viewed 3944

How in Postgresql inside a transaction to get values ​​into a variable, and if SELECT did not return anything, throw an error, and if SELECT returned data, then use them in the transaction?

Like this:

BEGIN;

@activeRounds = SELECT * FROM "rounds" WHERE status = 'active';

if(!@activeRounds) {
  RAISE ERROR "Has no Active rounds"
};

INSERT INTO "bet"
  (user_id, round_id)
VALUES
  (100, @activeRound[0].id)


COMMIT;

how to do something similar in one request within a transaction?

postgresql transactions sql
2 Answers

You can adapt following code to your table structure:

begin;
do
$$
declare
 v int;
begin
 select c1 into v from t1 where  k1=1;
 if not found 
 then
  raise exception 'no row found';
 else
  insert into t2(c2) values(v);
 end if;
end;
$$;
commit;

Note the difference bewteen begin; to start a transaction and begin to start a pl/pgSQL block.

user_107268500

Write a DO statement in PL/pgSQL.

Like every SQL statement, DO runs in a single transaction. PL/pgSQL is a procedural language that has variables and conditional processing.

user_64643080
Related