CODEWITHSUNDEEP
sqlpostgresqldefaultlocal-variablesdeclare
Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

DEFAULT local variables vs Initialized local variables with DECLARE in PostgreSQL

First, I experimented the DEFAULT local variable value which has 2 as shown below:

CREATE FUNCTION my_func() RETURNS INTEGER AS $$
DECLARE
  value INTEGER DEFAULT 2; -- Here
BEGIN
  IF value = 2 THEN
    value := 4;
    RETURN value;
  ELSE
    value := 2;
    RETURN value;
  END IF;
END;
$$ LANGUAGE plpgsql;

Then, my_func() returned 4 twice without 4 and 2 alternately as shown below:

postgres=# SELECT my_func();
 my_func
---------
       4
(1 row)
postgres=# SELECT my_func();
 my_func
---------
       4
(1 row)

Second, I experimented the initialized local variable value which has 2 as shown below:

CREATE FUNCTION my_func() RETURNS INTEGER AS $$
DECLARE
  value INTEGER := 2; -- Here
BEGIN
  IF value = 2 THEN
    value := 4;
    RETURN value;
  ELSE
    value := 2;
    RETURN value;
  END IF;
END;
$$ LANGUAGE plpgsql;

Then, my_func() returned 4 twice without 4 and 2 alternately as shown below:

apple=# SELECT my_func();
 my_func
---------
       4
(1 row)
apple=# SELECT my_func();
 my_func
---------
       4
(1 row)

So, what is the difference between DEFAULT local variables and initialized local variables?

Upvotes: 0

Views: 71

Answers (1)

Zegarek
Zegarek

Reputation: 26347

If your goal is to save a value, save it to a table. To persist it for the duration of a session, make it a temp table. For transaction, temp...on commit drop.

You can also use custom parameters and alter them from inside the function.

set my.var=2;

Otherwise, your value variable lives only for the duration of the function and it's reinitialised individually for each call.

set my.var=2;

CREATE FUNCTION my_func() RETURNS INTEGER AS $f$
BEGIN
  IF current_setting('my.var',true)::int = 2 THEN
    set my.var = 4;
    RETURN current_setting('my.var');
  ELSE
    set my.var = 2;
    RETURN current_setting('my.var');
  END IF;
END;
$f$ LANGUAGE plpgsql;

select my_func();
my_func
4 
select my_func();
select my_func();
select my_func();
my_func
2
my_func
4
my_func
2

Demo at db<>fiddle

Upvotes: 0

Related Questions