Re: greetings - Mailing list pgsql-sql

From Ian Lance Taylor
Subject Re: greetings
Date
Msg-id siwvafwu4h.fsf@daffy.airs.com
Whole thread Raw
In response to greetings  (Ken Kline <ken@oldbs.com>)
List pgsql-sql
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
ISNOT 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


pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: greetings
Next
From: Roberto Mello
Date:
Subject: Passing a table to PL/pgSQL