CODEWITHSUNDEEP
sqlpostgresqlplpgsql
nulltorpedo
nulltorpedo

Reputation: 1215

Call function multiple times in a loop

I have a function in my Postgres 8.3 database that takes a "key" variable as argument.
I want to call this function for a range of key values.

I tried this, didn't work ...

BEGIN
for i IN 773..775 LOOP
  test_count(i);
end LOOP;
end;

SQL error:
ERROR: syntax error at or near "for"
LINE 2: for i IN 773..775 LOOP

Upvotes: 3

Views: 8267

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658737

Procedural elements (typically PL/pgSQL) can only be run inside a code block - in a function or a DO command, not in plain SQL.

Also, when "calling" a function in PL/pgSQL you must care of the return value(s). If you have no further use for them, discard return values with PERFORM:

CREATE OR REPLACE FUNCTION foo()
  RETURNS void LANGUAGE plpgsql AS
$func$
BEGIN
   FOR i IN 773 .. 775
   LOOP
      PERFORM test_count(i);
   END LOOP;
END
$func$;

Upvotes: 4

JDValle
JDValle

Reputation: 83

Use a record type for your keys

DO $BODY$
DECLARE tmp_row record;
BEGIN

  FOR tmp_row IN (SELECT key from my_keys_table)
   LOOP
     PERFORM test_function(tmp_row.key);
  END LOOP;

END;
$BODY$;

Upvotes: 0

Mihai Stancu
Mihai Stancu

Reputation: 16127

PostgreSQL 8.3 cannot run anonymous procedures/ functions or create variables outside of a procedure/ function.

The DO construct was added as support for anonymous procedures as of version 9.0.

You should run your code inside a function. Because the error message you are receiving states that FOR is an unexpected keyword in a global context.

Upvotes: 0

Related Questions