PostgreSQL - declare variable and change its value multiple times

Question

It's a disaster that I, coming from MySQL and T-SQL, cannot figure out from documentation (0 real examples) and stackoverflow (0 real examples) how to deal with the most basic variable management in PostgreSQL.

Code below causes an error ERROR: duplicate declaration...

declare invalidThirdUser boolean := 'false';
    
invalidThirdUser = (select 'true' where NEW.sender_id not in(
    select user1_id from thread where id = NEW.thread_id
    union all
    select user2_id from thread where id = NEW.thread_id
));

I tried

invalidThirdUser = (select...

invalidThirdUser := (select...

set invalidThirdUser = (select...

select invalidThirdUser = (select...

How painful is assigning a new value to variable in postgres? Thanks.

Edit the question and provide a minimal reproducible example, in this case the CREATE statements of the table (paste the text, don't use images) and a DO block where you declare and assign to the variable (dito). Instead of the DO block a simple function definition would do to but is more complicated. — sticky bit
– sticky bit, Commented Mar 25, 2021 at 0:21
And by the way, the literals for Booleans are simply true and false, there's no need for the ' which makes them actually string literals that get -- unnecessarily -- implicitly casted. — sticky bit
– sticky bit, Commented Mar 25, 2021 at 0:23
And you can omit the subquery. Just new.sender_id NOT IN (...) is an expression that already evaluates to a Boolean value. — sticky bit
– sticky bit, Commented Mar 25, 2021 at 0:27

Oto Shavadze · Accepted Answer · 2021-03-25 00:31:31Z

1

This is very simple example, how to deal with the most basic variable management in PostgreSQL:

do $$
declare
    invalidThirdUser boolean DEFAULT FALSE;
begin
    RAISE NOTICE '%', invalidThirdUser; -- check variable's current value
    
    invalidThirdUser := TRUE; -- set new value  
    RAISE NOTICE '%', invalidThirdUser; -- check variable's current value
    
    SELECT FALSE INTO invalidThirdUser; -- set new value  again, but using SELECT..INTO way
    RAISE NOTICE '%', invalidThirdUser; -- check variable's current value

end; $$ language plpgsql

edited Mar 25, 2021 at 0:31

answered Mar 25, 2021 at 0:24

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user11067933 Over a year ago

Why it's impossible to declare variable inside begin...end block? It's like to be forced to declare variables outside function block in programming. Why it's impossible to select a variable to output is as single result set used by 3rd party applications?

Collectives™ on Stack Overflow

PostgreSQL - declare variable and change its value multiple times

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related