Thread: Error when trying to use a FOR loop
Sorry if this is in the archives, but I've done a search and couldn't find anything relevant. I'm running HP's
precompiledversion of 8.1.3.1 as part of their Internet Express offering, and I can't seem to run a for loop. Here's
whatI'm seeing:
xp512-0715-0716=# FOR LV in 1..10 LOOP
xp512-0715-0716-# select * from ldevrg;
ERROR: syntax error at or near "FOR" at character 1
LINE 1: FOR LV in 1..10 LOOP
^
I'm still pretty new to postgres, but based on the documentation I'm not picking up what I'm doing wrong.
Help much appreciated.
Thanks,
Kevin
On Thu, 20 Jul 2006, Kevin Nikiforuk wrote: > Sorry if this is in the archives, but I've done a search and couldn't > find anything relevant. I'm running HP's precompiled version of 8.1.3.1 > as part of their Internet Express offering, and I can't seem to run a > for loop. Here's what I'm seeing: > > xp512-0715-0716=# FOR LV in 1..10 LOOP > > xp512-0715-0716-# select * from ldevrg; > > ERROR: syntax error at or near "FOR" at character 1 > > LINE 1: FOR LV in 1..10 LOOP I think the problem is that the FOR is a pl/pgsql construct and isn't allowed in straight sql contexts but only inside a function.
> Sorry if this is in the archives, but I've done a search and couldn't find anything relevant. > I'm running HP's precompiled version of 8.1.3.1 as part of their Internet Express offering, and > I can't seem to run a for loop. Here's what I'm seeing: > xp512-0715-0716=# FOR LV in 1..10 LOOP > xp512-0715-0716-# select * from ldevrg; > ERROR: syntax error at or near "FOR" at character 1 > LINE 1: FOR LV in 1..10 LOOP The following is give examples of control structures. However, you need to be sure that your version of postgresql has a procedural language installed. If not, you will need to install it. http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS Use can use createlang to add different languages to postgresql. http://www.postgresql.org/docs/8.1/interactive/sql-createlanguage.html The following link gives a shot list of languages that you can use. There are others however: http://www.postgresql.org/docs/8.1/interactive/server-programming.html see Server-side Procedural Languages from: http://www.postgresql.org/download/ Hope this help. Regards, Richard Broersma Jr.
----- Original Message From: "Kevin Nikiforuk" <snip> >xp512-0715-0716=# FOR LV in 1..10 LOOP <snip> It appears that you are trying to use the For .. Loop structure from the psql command line. This structure is not plain SQL - its meant to be used within PL/pgSQL. Have you tried incorporating it into a PL/pgSQL function? See http://www.postgresql.org/docs/8.1/static/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS Regards, George
Many thanks to Stephan, Richard and George. When I was reading the documentation about FOR loops, I didn't realize
thatI was in the plpgsql section!
CREATE OR REPLACE FUNCTION rgio() RETURNS integer as $$
BEGIN DECLARE lv RECORD; FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP SELECT ldev FROM
ldevrgWHERE ldevrg='$lv'; END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql;
So I've made progress, but I'm running into the same thing.
psql:rgio.sql:16: ERROR: syntax error at or near "FOR" at character 86
psql:rgio.sql:16: LINE 6: FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP
psql:rgio.sql:16: ^
If I comment out the DECLARE statement above, I get the following:
psql:rgio.sql:16: ERROR: loop variable of loop over rows must be record or row variable at or near "LOOP" at character
129
psql:rgio.sql:16: LINE 6: FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP
psql:rgio.sql:16: ^
Ideas?
________________________________
From: pgsql-sql-owner@postgresql.org on behalf of Kevin Nikiforuk
Sent: Thu 7/20/2006 7:46 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Error when trying to use a FOR loop
Sorry if this is in the archives, but I've done a search and couldn't find anything relevant. I'm running HP's
precompiledversion of 8.1.3.1 as part of their Internet Express offering, and I can't seem to run a for loop. Here's
whatI'm seeing:
xp512-0715-0716=# FOR LV in 1..10 LOOP
xp512-0715-0716-# select * from ldevrg;
ERROR: syntax error at or near "FOR" at character 1
LINE 1: FOR LV in 1..10 LOOP
^
I'm still pretty new to postgres, but based on the documentation I'm not picking up what I'm doing wrong.
Help much appreciated.
Thanks,
Kevin
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
am 21.07.2006, um 9:26:21 -0600 mailte Kevin Nikiforuk folgendes: > Many thanks to Stephan, Richard and George. When I was reading the documentation about FOR loops, I didn't realize thatI was in the plpgsql section! > > CREATE OR REPLACE FUNCTION rgio() RETURNS integer as $$ > BEGIN > DECLARE lv RECORD; lv is a record... > FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP untested: tv.rg HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net=== Schollglas Unternehmensgruppe ===
"Kevin Nikiforuk" <Kevin.Nikiforuk@Acrodex.com> writes:
> CREATE OR REPLACE FUNCTION rgio() RETURNS integer as $$
> BEGIN
> DECLARE lv RECORD;
> FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP
> SELECT ldev FROM ldevrg WHERE ldevrg='$lv';
> END LOOP;
> RETURN 1;
> END;
> $$ LANGUAGE plpgsql;
You've got a small error in the layout: the DECLARE part goes before
BEGIN not after. Swap the first two lines of the function.
regards, tom lane
On Fri, 21 Jul 2006, Kevin Nikiforuk wrote: > Many thanks to Stephan, Richard and George. When I was reading the documentation about FOR loops, I didn't realize thatI was in the plpgsql section! > > CREATE OR REPLACE FUNCTION rgio() RETURNS integer as $$ > BEGIN > DECLARE lv RECORD; You'd want to put declare first (it goes before begin). > FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP > SELECT ldev FROM ldevrg WHERE ldevrg='$lv'; I think you'd want something like ldevrg=lv.rg. > END LOOP; What's the final intent for this since AFAICS this is just going to do busy work that throws away the results. If you wanted to see the results of each of these selects you have to do a bit more work.