PostgreSQL loops outside functions. Is that possible?

Question

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.

Solution 1

You cannot DECLARE (global) variables (there are workarounds) nor loop with plain SQL - with the exception of recursive CTEs as provided by @bma (which is actually iterating over rows, not looping, strictly speaking).

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 take any parameters and 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 defaults to 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 ...

Solution 2

You can recursively query result sets using WITH RECURSIVE, assuming you are on Postgresql 8.4+. Docs: http://www.postgresql.org/docs/current/static/queries-with.html

This would allow you to loop your set and process the data in various ways.

Comments

[JGutierrezC]

Thank you so much bma. But can i use Declare keyword? because sometimes you need to insert for example, or depending on some contidition if the condition is true insert else if... update... else delete... am i clear? Best regards.

[bma]

"Declare", no, but using the WITH clause, you are able to submit a value in one of the topmost clauses, and reference that multiple times in subsequent locations of the query (also known as "chaining"). For example, in the link I supplied, the "regional_sales" section is used as the source for the subsequent section (named "top_regions"). That illustrates the query chaining I mentioned. In Postgresql 9.2+, you can use WITH clauses (aka "Common Table Expressions" (CTE)) to do "upsert" statements (which you referred to as the "insert inf true, else update, else delete")

[IMSoP]

It's worth reiterating more strongly that if you can re-cast your problem in terms of sets rather than loops, do so - that is, if you can build an SQL query that works on the results in one go, it is likely to perform much better than resorting to procedural code.