From: "Foster, Stephen" <stephenlfoster@comcast.net>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>
Cc: "'Michael Fuhr'" <mike@fuhr.org>, <pgsql-general@postgresql.org>,
	<pgsql-sql@postgresql.org>
Subject: Re: [SQL] Looking for information on PostgreSQL Stored Procedures 
Date: Sun, 11 Dec 2005 11:47:29 -0600
Message-ID: <000001c5fe7a$f95c2d00$2101a8c0@cfgod>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="windows-1250"
Content-Transfer-Encoding: 7bit
Importance: Normal
In-Reply-To: <17301.1134320658@sss.pgh.pa.us>

Trying a totally different approach.  Simple procedure that I'm using to
use as learn opportunity in stored procedures in PostgreSQL prior to
migrating a complex web site.  Call it a training example if you will.
Goal is to learn the correct process of working with cursors on complex
queries in PostgreSQL before investing hours of work migrating a web
site and moving some of the complex procedures to the database to
simplify a process.

Using this simple example,

CREATE FUNCTION sp_removedups() RETURNS void AS
$BODY$
DECLARE 
	lastname varchar(255);
	fname varchar(255);
	id bigint;
DECLARE NewListCursor CURSOR FOR
	SELECT	Name, id
	FROM	MailingList
	ORDER BY Name;
BEGIN
	OPEN NewListCursor;
	LastName := "";
	FETCH NEXT FROM NewListCursor INTO fname, id;
	WHILE (--Lost on variable name for end of query;
EmptyQueryResponse <> 0? --)
		BEGIN
			IF LastName = fname THEN
				DELETE FROM MailingList WHERE id = id;
			END IF;
			LastName := fname;
			FETCH NEXT FROM NewListCursor INTO fname, id;
		END;
	CLOSE NewListCursor;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

On the BEGIN/END; yes you are right it was missing.  I'm converting a
MS-2000 SQL example to PostgreSQL.  Error according to pgadminIII is on
line 11.  Which should be the "LastName := "";" line.  Before someone
says it there is a better way of do this but it was a simple example
before I dived in to the real ones.

Second question/problem is how do you determine if the query has reached
the end.  Mentioned on the WHILE line.

I have another project after this one I'm about to tackle that will be
even more complex.  So the sooner I can grasp the store procedures in
this database the better off I will be.  If there is a book somewhere
that will clearly define the command set please let me know.  This way
determine what I can and can't do.  The web language I use I can go back
and fore with no problems but am think some of the processing that it is
currently doing would be faster if I move it to the database side.

Thanks for any help,

Lee Foster

-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date:
12/9/2005