77

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.

Community
  • 1
  • 1
UnDiUdin
  • 14,924
  • 39
  • 151
  • 249
  • this post might help? http://stackoverflow.com/questions/36959/how-do-you-use-script-variables-in-postgresql – jheppinstall Jul 01 '10 at 12:36
  • Possible identical to http://stackoverflow.com/questions/36959/how-do-you-use-script-variables-in-postgresql – Gabriele Petrioli Jul 01 '10 at 12:36
  • Seems like you have to wait for 9.0 - ["DO -- execute an anonymous code block"](http://developer.postgresql.org/pgdocs/postgres/sql-do.html). – Milen A. Radev Jul 01 '10 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 Jul 01 '10 at 14:02

1 Answers1

109

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.

araqnid
  • 127,052
  • 24
  • 157
  • 134
  • the wire protocol allows several commands. and thus several different result sets (in asynchronous mode). – Jasen Mar 29 '16 at 01:12
  • @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. – araqnid Mar 29 '16 at 12:23
  • 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. – Jasen Mar 29 '16 at 21:46