Re: [SQL] Looking for information on PostgreSQL Stored Procedures

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