From: Foster, Stephen <stephenlfoster@comcast.net>
To: 'Tom Lane' <tgl@sss.pgh.pa.us>
Cc: 'Michael Fuhr' <mike@fuhr.org>
Cc: pgsql-general@postgresql.org
Cc: 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> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: stephenlfoster@comcast.net, tgl@sss.pgh.pa.us, mike@fuhr.org, pgsql-general@postgresql.org
Subject: Re: [SQL] Looking for information on PostgreSQL Stored Procedures
In-Reply-To: <000001c5fe7a$f95c2d00$2101a8c0@cfgod>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox