Received: from foghorn.airs.com (foghorn.airs.com [63.201.54.26]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f1ONFIx14083 for ; Sat, 24 Feb 2001 18:15:18 -0500 (EST) (envelope-from ian@airs.com) Received: (qmail 24412 invoked by uid 10); 24 Feb 2001 23:15:15 -0000 Received: (qmail 10777 invoked by uid 269); 24 Feb 2001 23:15:11 -0000 To: Ken Kline Cc: Tom Lane , pgsql-sql@postgresql.org Subject: Re: greetings References: <3A974086.EF665152@oldbs.com> <11378.982997569@sss.pgh.pa.us> <3A980760.87DAC5AC@oldbs.com> From: Ian Lance Taylor Date: 24 Feb 2001 15:15:10 -0800 In-Reply-To: Ken Kline's message of "Sat, 24 Feb 2001 14:11:28 -0500" Message-ID: Lines: 50 User-Agent: Gnus/5.0807 (Gnus v5.8.7) Emacs/20.7 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200102/388 X-Sequence-Number: 1593 Ken Kline 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