From: Ian Lance Taylor <ian@airs.com>
To: Ken Kline <ken@oldbs.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: greetings
Date: 24 Feb 2001 15:15:10 -0800
Message-ID: <siwvafwu4h.fsf@daffy.airs.com> (raw)
References: <3A974086.EF665152@oldbs.com>
<siae7c1tso.fsf@daffy.airs.com>
<11378.982997569@sss.pgh.pa.us>
<3A980760.87DAC5AC@oldbs.com>
Ken Kline <ken@oldbs.com> writes:
> it is to be server side code....
> the code I gave you was merely an example
> of a cursor that I found when I did a search...
> http://www.armed.net/how/pg001676.htm
I think Tom addressed how to solve your actual problem.
I'll just comment that the example you see is of pure SQL being passed
in from a client. If you want to use a loop, you need to use
PL/pgSQL. And PL/pgSQL doesn't currently support cursors.
> DECLARE
> CURSOR get_rows AS
> SELECT DISTINCT pseason, pyear FROM load_members
> WHERE pyear IS NOT NULL
> AND pseason IS NOT NULL
> ORDER BY pyear, pseason;
> BEGIN
> FOR rec IN get rows LOOP
> INSERT INTO pledge_classes (semester, year)
> VALUES
> (rec.pseason, rec.pyear);
> END LOOP;
> COMMIT;
> END;
You can do this in PL/pgSQL by just doing the FOR over the SELECT.
This is untested, but it shows the basic idea.
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT DISTINCT pseason, pyear FROM load_members
WHERE pyear IS NOT NULL
AND pseason IS NOT NULL
ORDER BY pyear, pseason;
LOOP
INSERT INTO pledge_classes (semester, year)
VALUES (rec.pseason, rec.pyear);
END LOOP;
COMMIT;
END;
But as Tom said there is no reason to sort records when inserting
them, since it doesn't make any difference what order they are in in
the table.
Ian
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: ian@airs.com, ken@oldbs.com, tgl@sss.pgh.pa.us
Subject: Re: greetings
In-Reply-To: <siwvafwu4h.fsf@daffy.airs.com>
References: <3A974086.EF665152@oldbs.com> <siae7c1tso.fsf@daffy.airs.com> <11378.982997569@sss.pgh.pa.us> <3A980760.87DAC5AC@oldbs.com> <siwvafwu4h.fsf@daffy.airs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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