如何在 postgresql 中声明局部变量?

2022-01-23 00:00:00 postgresql migration sql-server

有一个几乎相同但没有真正回答的问题 这里.

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

我正在将应用程序从 MS SQL Server 迁移到 PostgreSQL.在代码中的许多地方我都使用局部变量，所以我想进行需要较少工作的更改，请问您能告诉我哪种方法是翻译以下代码的最佳方法吗?

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)

如果 Postgresql 8.4 甚至 9.0 包含可以简化翻译的重要功能，我将使用它.

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

推荐答案

历史上，Postgresql 不支持命令级别的过程代码 - 仅在函数内.但是，在 Postgresql 9 中，已将支持添加到 执行内联代码块 有效地支持这样的事情，虽然语法可能有点奇怪，并且与您可以使用 SQL Server 执行的操作相比存在许多限制.值得注意的是，内联代码块无法返回结果集，因此不能用于您上面概述的内容.

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();

据我所知，PostgreSQL 有线协议不允许诸如返回多个结果集的命令之类的事情.因此，您不能简单地将 T-SQL 批处理或存储过程映射到 PostgreSQL 函数.

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.

相关文章