Re: trying to learn plpqsql... so please forgive..

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