How To 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
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.
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.
- → How to make Laravel use my class instead of native PDO?
- → SQL: simple custom column in select doesn't work?
- → How to execute Stored Procedure from Laravel
- → Which database engine type should be specified for Microsoft SQL Database in Laravel?
- → How to troubleshoot PDOException?
- → laravel sql server stored procedure output
- → Issue with converting a date using Carbon
- → SQL microsoft query to Laravel 4.2
- → General error 20018 Cannot Continue the file execution because the session is in the Kill state
- → List names of all available MS SQL databases on server using python
- → Variable which replace DB of name in SSMS
- → Java: database connection. Where is my mistake?
- → How Can I use "Date" Datatype in sql server?