Message-Id: <5.1.0.14.0.20021120225235.00b71db8@192.168.1.3>
Date: Wed, 20 Nov 2002 23:14:16 +0100
To: "Josh Berkus" <josh@agliodbs.com>, pgsql-sql@postgresql.org
From: Michiel Lange <michiel@minas.demon.nl>
Subject: Re: trying to learn plpqsql... so please forgive..
Cc: Michiel Lange <michiel@minas.demon.nl>, pgsql-sql@postgresql.org
In-Reply-To: <web-1834137@davinci.ethosmedia.com>
References: <5.1.0.14.0.20021120010436.00b4c920@192.168.1.3>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

To those who pointed at the SELECT currval <pkey-name>, thanks!, I think 
this is what I need instead of a trigger. So the real problem is solved I 
think. However I am quite curious about the plpgsql thing, I think I may 
need to use it, or may WANT to use it (performance wise... better to be as 
close as possible to the database whenever possible is one of my mottos ;->)

So I will go in more detail about the case, as I am one of those people who 
can hardly learn from books, but far more by seeing a case and a 
solution... then apply it to some other problem instead... a strength and 
weakness in one ;->

Let's say I created this table

CREATE TABLE mytable(
         my_key SERIAL NOT NULL PRIMARY KEY,
         row1 VARCHAR(5),
         row2 VARCHAR(15),
         row3 TEXT);

And this function:
CREATE FUNCTION add_cust() RETURNS INT4 AS ' -- SERIAL data type is really 
an INT4 (and some more).
BEGIN
         RETURN NEW.my_key;
END;
' LANGUAGE 'plpgsql';

  CREATE TRIGGER add_cust BEFORE INSERT ON mytable
  FOR EACH ROW EXECUTE PROCEDURE add_cust();

Ok, now I know it won't work... the idea was to use this with PHP in a 
webclient interface where the customer could give some information about 
him/herself and then would be registered with the customer number generated 
by the SERIAL type.
Would it work if I did a CREATE TRIGGER add_cust AFTER INSERT... ? (mention 
the AFTER instead of BEFORE)

Please mind that the problem has now migrated to solved but still curious 
*g* :)

Michiel

At 08:49 20-11-2002 -0800, Josh Berkus wrote:
>Michiel,
>
> > Maybe this should be sent to novice... I was not certain, but if it
> > should, please tell me so.
>
>No need to apologise.   Novice would have been appropriate, but SQL is
>OK too.
>
> > When I do an 'INSERT INTO <table> VALUES <row1,row2,row3>'
> > and on the table is a serial primary key named p_key.
> > As I want this number to be auto-generated, but use it as a 'customer
> > number', I want to create this function to return the value of this
> > insert. I thought/hoped that this would work, but as there are some
> > people dependant on this database, I dare not try out too much ;-)
> > This would be the first time I used plpgsql, so I am not so certain
> > about what I do.
>
>Hmmm ... the trigger, as you've written it, won't work.   An INSERT
>  trigger can modify the inserted data, or reject it, or update data in
>other tables.  But it cannot return data to the screen.
>
>Can you break down, in more detail, what you're *trying* to do?   It
>can probably be done, but I'm still not clear on what you're
>attempting.
>
>-Josh Berkus