CODEWITHSUNDEEP
sqldatabasepostgresqllocal-variablesdeclare
UnDiUdin
UnDiUdin

Reputation: 15394

Declare local variables in PostgreSQL?

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.

Upvotes: 79

Views: 170907

Answers (2)

araqnid
araqnid

Reputation: 133712

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.

Upvotes: 114

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 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)

Upvotes: 1

Related Questions