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

How to declare local variables in postgresql?(如何在 postgresql 中声明局部变量?)

问题描述

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

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.

这篇关于如何在 postgresql 中声明局部变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！