mysql什么是外循环和内循环,PostgreSQL在函数之外循环。那可能吗？

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 ...