Call function multiple times in a loop

Question

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

SQL error: ERROR: syntax error at or near "for" LINE 2: for i IN 773..775 LOOP — nulltorpedo
– nulltorpedo, Commented Jun 22, 2012 at 21:37
@a_horse meant you should update your question and mention the error message. If it's in the comments anyone trying to answer your question will have to read every comment to find bits and pieces of info. — Mihai Stancu
– Mihai Stancu, Commented Jun 22, 2012 at 21:39
As others have pointed out, this would be easier in version 9.0 or 9.1 (or 9.2, which should be released later this summer). Version 8.3 will hit end-of-life in about 8 months. When you upgrade, you should strongly consider jumping over 8.4 and 9.0 and going to 9.1 or later. — kgrittn
– kgrittn, Commented Jun 23, 2012 at 15:50

Erwin Brandstetter · Accepted Answer · 2025-01-21 14:59:51Z

4

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$;

edited Jan 21 at 14:59

answered Jun 22, 2012 at 23:25

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mihai Stancu · Accepted Answer · 2012-06-22 23:12:39Z

0

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.

edited Jun 22, 2012 at 23:12

answered Jun 22, 2012 at 21:38

Mihai Stancu

16.2k2 gold badges35 silver badges51 bronze badges

6 Comments

user330315 Over a year ago

It can, just not that outdated version he is using.

Mihai Stancu Over a year ago

I'm updating the answer i forgot to mention the DO construct in 9+ indeed.

Mike Christensen Over a year ago

@a_horse_with_no_name - Wait what version of PG does that work on?

Mihai Stancu Over a year ago

I've already statet that in my answer as well as my comment here. The version is > 9.

Erwin Brandstetter Over a year ago

@MihaiStancu: Major versions in PostgreSQL are denominated by the first two parts of the number, i.e. 8.4, 9.0, 9.1, ... Strictly speaking, there is no such thing as "version 9". Read up about PostgreSQL versioning here. The DO statement was announced in the release notes for 9.0 here.

|

JDValle · Accepted Answer · 2021-04-29 21:39:49Z

0

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$;

answered Apr 29, 2021 at 21:39

JDValle

831 gold badge2 silver badges8 bronze badges

Collectives™ on Stack Overflow

Call function multiple times in a loop

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related