Declare local variables in PostgreSQL?

Question

There is an almost identical, but not really answered question here.

I am migrating an application from MS SQL Server to PostgreSQL. In many places in code I use local variables so I would like to go for the change that requires less work, so could you please tell me which is the best way to translate the following code?

-- MS SQL Syntax: declare 2 variables, assign value and return the sum of the two 
declare @One integer = 1
declare @Two integer = 2
select @One + @Two as SUM

this returns:

SUM
-----------
3

(1 row(s) affected)

I will use Postgresql 8.4 or even 9.0 if it contains significant fetaures that will simplify the translation.

Seems like you have to wait for 9.0 - "DO -- execute an anonymous code block". — Milen A. Radev
– Milen A. Radev, Commented Jul 1, 2010 at 12:44
@jeppinstall: yes they say You need to use one of the procedural languages such as PL/pgSQL not the SQL proc language... So which is the PL/pgSQL syntax for that? — UnDiUdin
– UnDiUdin, Commented Jul 1, 2010 at 14:02

atomsmith · Accepted Answer · 2024-05-31 12:35:09Z

115

Postgresql historically doesn't support procedural code at the command level - only within functions. However, in Postgresql 9, support has been added to execute an inline code block that effectively supports something like this, although the syntax is perhaps a bit odd, and there are many restrictions compared to what you can do with SQL Server. Notably, the inline code block can't return a result set, so can't be used for what you outline above.

In general, if you want to write some procedural code and have it return a result, you need to put it inside a function. For example:

CREATE OR REPLACE FUNCTION somefuncname() RETURNS int LANGUAGE plpgsql AS $$
DECLARE
  one int;
  two int;
BEGIN
  one := 1;
  two := 2;
  RETURN one + two;
END
$$;
SELECT somefuncname();

The PostgreSQL wire protocol doesn't, as far as I know, allow for things like a command returning multiple result sets. So you can't simply map T-SQL batches or stored procedures to PostgreSQL functions.

edited May 31, 2024 at 12:35

atomsmith

1134 bronze badges

answered Jul 1, 2010 at 13:14

araqnid

135k25 gold badges163 silver badges138 bronze badges

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

4 Comments

Jasen Over a year ago

the wire protocol allows several commands. and thus several different result sets (in asynchronous mode).

araqnid Over a year ago

@Jasen I specifically meant a single query returning multiple result sets/result counts, rather than having several query-response cycles in flight. Although even that situation may have changed since 2010.

Jasen Over a year ago

it may be possible to re-write the task as several calls. select * from f1(); select * from f2(); select * from f3(); and so receive several different result sets.

snorbi Over a year ago

The "execute an inline code block" link is broken.

blackgreen · Accepted Answer · 2024-02-19 11:28:57Z

1

You can declare local variables with :=, = in DECLARE clause as shown below:

CREATE FUNCTION my_func()
RETURNS INT
AS $$
DECLARE
  value1 INT := 1; -- Here
  value2 INT = 2; -- Here
  value3 INT DEFAULT 3; -- Here
  value4 CONSTANT INT := 4; -- Here
  value5 INT; -- Here
BEGIN
  RETURN value1 + value2 + value3;
END;
$$ LANGUAGE plpgsql;

*Memos:

:=, = and DEFAULT are the same.
Trying to change the constant local variable value4 gets error.
The uninitialized local variable value5 is NULL.
You can declare local variables with DECLARE clause in a PL/pgSQL function and procedure and DO statement.

Then, calling my_func() returns 6 as shown below:

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

edited Feb 19, 2024 at 11:28

blackgreen♦

46.8k29 gold badges171 silver badges162 bronze badges

answered Dec 9, 2023 at 23:29

Super Kai - Kazuya Ito

42.7k23 gold badges258 silver badges257 bronze badges

Collectives™ on Stack Overflow

Declare local variables in PostgreSQL?

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related