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