Variables in looping condition in a postgreSQL while loop - Stack Overflow

admin管理员组

文章数量:1417712

I'm using psql to process a sql file.
A parameter is sent to the psql call as:

-v myVar=5

The SQL file needs to loop from 1 till myVar and do some stuff. i is a being declared as DECLARE i integer = 1;

I have tried the following syntax/s on the WHILE clause

WHILE i <= :myVar LOOP and
WHILE i <= myVar LOOP

But psql is reporting

ERROR: syntax error at or near ":
LINE 6: WHILE i <= :myVar LOOP
^ and

ERROR: column "myVar " does not exist
LINE 1: i <= myVar

The whole loop is defined like this:

DO
$do$
DECLARE i integer = 1;
BEGIN
    WHILE i <= :myVar LOOP
        -- do some stuff with i
        i := i + 1;
    END LOOP;
END;
$do$

I'm using psql to process a sql file.
A parameter is sent to the psql call as:

-v myVar=5

The SQL file needs to loop from 1 till myVar and do some stuff. i is a being declared as DECLARE i integer = 1;

I have tried the following syntax/s on the WHILE clause

WHILE i <= :myVar LOOP and
WHILE i <= myVar LOOP

But psql is reporting

ERROR: syntax error at or near ":
LINE 6: WHILE i <= :myVar LOOP
^ and

ERROR: column "myVar " does not exist
LINE 1: i <= myVar

The whole loop is defined like this:

DO
$do$
DECLARE i integer = 1;
BEGIN
    WHILE i <= :myVar LOOP
        -- do some stuff with i
        i := i + 1;
    END LOOP;
END;
$do$

• postgresql
• while-loop
• psql

Share Improve this question Follow asked Jan 31 at 8:35 Ron TuffinRon Tuffin 54.7k2424 gold badges6767 silver badges7878 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

The problem is that the body of the DO statement is a dollar-quoted string literal, and there is no variable substitution in string literals.

With psql, you could use \gexec:

SELECT format('DO
$do$
DECLARE i integer = 1;
BEGIN
    WHILE i <= %L::integer LOOP
        -- do some stuff with i
        i := i + 1;
    END LOOP;
END;
$do$', :myVar) \gexec

The %L will be replaced with the (singly quoted) value of myVar, and the result of the SELECT statement (the DO statement) will be executed.

I use %L to get a string literal and cast it to integer. The point of that seemingly useless exercise is to avoid problems with SQL injection.

本文标签： Variables in looping condition in a postgreSQL while loopStack Overflow