Re: [GENERAL] Looking for information on PostgreSQL Stored Procedures - Mailing list pgsql-sql

Michael,

This is one of the simple ones that has me hung up.  It currently errors
out on the first line.  Why I have no idea.  It looks right to me.  One
of the harder ones that I have to work on is on the security
sub-routines to pull together the security rights for a person during
login and I'm looking forward to that one.

I'll take a look at the reference that you sent.  The other ones that I
wrote had to be really simple if I'm hung on this one.  It is just a
simple purge on a temp table before merging new information into the
master table.  I have thought about this before hand wasn't about to
locate the "Welcome to PostreSQL Stored Procedures" or in my current
case "PostreSQL for idiots".  This is embarrassing for the time I've
spend as a DBA.  Getting kicked by a simple pre-process procedure.

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;
OPEN NewListCursor;
LineNum := 0;
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;
$BODY$
LANGUAGE 'sql' VOLATILE;


-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Michael Fuhr
Sent: Saturday, December 10, 2005 10:41 PM
To: Foster, Stephen
Cc: pgsql-general@postgresql.org; pgsql-sql@postgresql.org
Subject: Re: [SQL] [GENERAL] Looking for information on PostgreSQL
Stored Procedures

On Sat, Dec 10, 2005 at 09:02:39PM -0600, Foster, Stephen wrote:
> I did see last week something on PLPGSQL and read through that.  But
> there has to be something out there that goes in depth on the
> SQL/Function command set(Speaking of functions/procedures).

The standard functions are described in the "Functions and Operators"
chapter of the documentation.  Here's a link to the latest version,
but use the documentation for the version you're running:

http://www.postgresql.org/docs/8.1/interactive/functions.html

SQL functions are documented in "Query Language (SQL) Functions":

http://www.postgresql.org/docs/8.1/interactive/xfunc-sql.html

> The biggest hole that I have; seems to be on Cursors; define and
> opening.  I think the fetching and closing is pretty straight forward.
> But the Define and opening is causing some grief.  Either I'm making
to
> far too hard or I'm really have missing something silly.

What problems are you having?  Without seeing what you're doing in
PostgreSQL it's difficult to say what's wrong.

In PL/pgSQL you can loop through query results without explicitly
using a cursor; see "Looping Through Query Results":

http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structure
s.html#PLPGSQL-RECORDS-ITERATING

[snip example]

> This is an example of the simple stored procedures like the ones I'm
> trying to migrate.  PLPGSQL is ok but I thought it would run better in
> SQL.

SQL doesn't have control structures, so if you need conditionals
or loops then use a procedural language like PL/pgSQL or implement
the logic on the client side.

For the example you posted, the following query should have the same
effect (remove records with duplicate names, if I'm reading it right):

DELETE FROM mailinglist
WHERE id NOT IN (
  SELECT DISTINCT ON (name) id
  FROM mailinglist
  ORDER BY name, id
);

I don't know how well this would perform on large data sets,
especially in older versions of PostgreSQL, but you could try it.
I'd recommend trying it first on a test table or in a transaction
that you can roll back in case it doesn't do what you want.

See the SELECT documentation for a description of the non-standard
DISTINCT ON clause that the above query uses:

http://www.postgresql.org/docs/8.1/interactive/sql-select.html

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

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


--
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