PostgreSQL在函数之外循环。那可能吗？ [英] PostgreSQL loops outside functions. Is that possible?

问题描述

为了迁移的目的，我正在对PostgreSQL与SQLServer进行比较。现在我正在评估T-SQL与PL / pgSQL，问题是在T-SQL中可以使用循环或声明变量，例如：

  declare @counter int 
 set @counter = 0 
 @counter< 10 
 begin 
 set @counter = @counter + 1 
 print'计数器是'+ cast（@counter as char）
 end 
  

不需要将它放入函数或过程中。我可以在PostgreSQL中做到这一点吗？

在网上搜索我发现一个否定答案在MySQL中执行它，但我没有为Postgres找到这样的答案。 解决方案不能 DECLARE （全局）变量（ ），也不会循环显示普通SQL - 例外是由@bma提供的递归CTE 。

然而，有 DO 声明。用Postgres 9.0引入。它像一次性函数一样工作，但不返回任何内容。您可以 RAISE 通知等等，所以你的例子可以正常工作：

  DO 
 $做$ 
 DECLARE 
 _counter int：= 0; 
 BEGIN 
 WHILE _counter< 10 
 LOOP 
 _counter：= _counter + 1; 
 RAISE NOTICE'柜台是％'，_counter; - 强制文本自动
 END LOOP; 
 END 
 $ do $ 
  

如果没有另行指定，正文是 plpgsql 。您可以使用任何注册程序语言，但如果您声明了（例如： LANGUAGE plpython ）。

Postgres还提供 generate_series（） 生成集合ad-hoc，在许多情况下避免了循环的需要。 尝试搜索这里的例子。

另外，您可以在 data-modifying CTE in plain SQL to fork cases and emulate IF .. THEN .. ELSE .. END ...

I'm making comparative about PostgreSQL vs. SQLServer for migrating purposes. Now I'm evaluating T-SQL vs. PL/pgSQL, the thing is that in T-SQL you can use loops or declare variables, for example:

declare @counter int
set @counter = 0
while @counter < 10
begin
   set @counter = @counter + 1
   print 'The counter is ' + cast(@counter as char)
end

There is no need to put it inside a function or procedure. Can I do that in PostgreSQL?

Searching on the web I found a negative answer doing it in MySQL but I didn't find such answer for Postgres.

解决方案

You cannot DECLARE (global) variables (well, there are ways around this) nor loop with plain SQL - with the exception of recursive CTEs as provided by @bma.

However, there is the DO statement for such ad-hoc procedural code. Introduced with Postgres 9.0. It works like a one-time function, but does not return anything. You can RAISE notices et al, so your example would just work fine:

DO
$do$
DECLARE
   _counter int := 0;
BEGIN
   WHILE _counter < 10
   LOOP
      _counter := _counter + 1;
      RAISE NOTICE 'The counter is %', _counter;  -- coerced to text automatically
   END LOOP;
END
$do$

If not specified otherwise, the language in the body is plpgsql. You can use any registered procedural language though, if you declare it (like: LANGUAGE plpython).

Postgres also offers generate_series() to generate sets ad-hoc, which may obviate the need for looping in many cases. Try a search here on SO for examples.

Also, you can use the WHERE clause in a data-modifying CTE in plain SQL to fork cases and emulate IF .. THEN .. ELSE .. END ...

这篇关于PostgreSQL在函数之外循环。那可能吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！