pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedError when trying to use a FOR loop
8+ messages / 6 participants
[nested] [flat]
* Error when trying to use a FOR loop
@ 2006-07-20 13:46 Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>
2006-07-20 20:19 ` Re: Error when trying to use a FOR loop Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-07-20 20:25 ` Re: Error when trying to use a FOR loop Richard Broersma Jr <rabroersma@yahoo.com>
2006-07-20 20:45 ` Re: Error when trying to use a FOR loop George Weaver <gweaver@shaw.ca>
0 siblings, 3 replies; 8+ messages in thread
From: Kevin Nikiforuk @ 2006-07-20 13:46 UTC (permalink / raw)
To: pgsql-sql
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'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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Error when trying to use a FOR loop
2006-07-20 13:46 Error when trying to use a FOR loop Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>
@ 2006-07-20 20:19 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2 siblings, 0 replies; 8+ messages in thread
From: Stephan Szabo @ 2006-07-20 20:19 UTC (permalink / raw)
To: Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Error when trying to use a FOR loop
2006-07-20 13:46 Error when trying to use a FOR loop Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>
@ 2006-07-20 20:25 ` Richard Broersma Jr <rabroersma@yahoo.com>
2 siblings, 0 replies; 8+ messages in thread
From: Richard Broersma Jr @ 2006-07-20 20:25 UTC (permalink / raw)
To: Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>; pgsql-sql
> 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-STRUC...
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Error when trying to use a FOR loop
2006-07-20 13:46 Error when trying to use a FOR loop Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>
@ 2006-07-20 20:45 ` George Weaver <gweaver@shaw.ca>
2 siblings, 0 replies; 8+ messages in thread
From: George Weaver @ 2006-07-20 20:45 UTC (permalink / raw)
To: Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>; pgsql-sql
----- 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...
Regards,
George
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Error when trying to use a FOR loop
@ 2006-07-21 15:26 Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>
2006-07-21 15:39 ` Re: Error when trying to use a FOR loop A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-07-21 15:40 ` Re: Error when trying to use a FOR loop Tom Lane <tgl@sss.pgh.pa.us>
2006-07-21 16:01 ` Re: Error when trying to use a FOR loop Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 3 replies; 8+ messages in thread
From: Kevin Nikiforuk @ 2006-07-21 15:26 UTC (permalink / raw)
To: pgsql-sql
Many thanks to Stephan, Richard and George. When I was reading the documentation about FOR loops, I didn't realize that I 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 ldevrg WHERE 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 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'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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Error when trying to use a FOR loop
2006-07-21 15:26 Re: Error when trying to use a FOR loop Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>
@ 2006-07-21 15:39 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2 siblings, 0 replies; 8+ messages in thread
From: A. Kretschmer @ 2006-07-21 15:39 UTC (permalink / raw)
To: pgsql-sql
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 that I 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 ===
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Error when trying to use a FOR loop
2006-07-21 15:26 Re: Error when trying to use a FOR loop Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>
@ 2006-07-21 15:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2006-07-21 15:40 UTC (permalink / raw)
To: Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Error when trying to use a FOR loop
2006-07-21 15:26 Re: Error when trying to use a FOR loop Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>
@ 2006-07-21 16:01 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2 siblings, 0 replies; 8+ messages in thread
From: Stephan Szabo @ 2006-07-21 16:01 UTC (permalink / raw)
To: Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>; +Cc: pgsql-sql
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 that I 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-07-21 16:01 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-20 13:46 Error when trying to use a FOR loop Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>
2006-07-20 20:19 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-07-20 20:25 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-07-20 20:45 ` George Weaver <gweaver@shaw.ca>
2006-07-21 15:26 Re: Error when trying to use a FOR loop Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>
2006-07-21 15:39 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-07-21 15:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-21 16:01 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox