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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
