Re: trying to learn plpqsql... so please forgive.. - Mailing list pgsql-sql

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