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

<p><font size="2">Michiel Lange wrote:</font><br /><font size="2">> Maybe this should be sent to novice... I was not
certain,but if it</font><br /><font size="2">> should, please tell me so.</font><br /><font size="2">> </font><br
/><fontsize="2">> The matter at hand is this:</font><br /><font size="2">> </font><br /><font size="2">> When
Ido an 'INSERT INTO <table> VALUES <row1,row2,row3>'</font><br /><font size="2">> and on the table is a
serialprimary key named p_key.</font><br /><font size="2">> As I want this number to be auto-generated, but use it
asa 'customer</font><br /><font size="2">> number', I want to create this function to return the value of
this</font><br/><font size="2">> insert. I thought/hoped that this would work, but as there are some</font><br
/><fontsize="2">> people dependant on this database, I dare not try out too much ;-)</font><br /><font size="2">>
Thiswould be the first time I used plpgsql, so I am not so certain</font><br /><font size="2">> about what I do.
</font><br/><font size="2">> </font><br /><font size="2">> CREATE FUNCTION add_cust() RETURNS INT4 AS ' -- SERIAL
datatype is</font><br /><font size="2">> really an INT4 (and some more).</font><br /><font size="2">>     
BEGIN</font><br/><font size="2">>          RETURN NEW.p_key;</font><br /><font size="2">>      END;</font><br
/><fontsize="2">> ' LANGUAGE 'plpgsql';</font><br /><font size="2">> </font><br /><font size="2">> CREATE
TRIGGERadd_cust BEFORE INSERT ON table</font><br /><font size="2">>      FOR EACH ROW EXECUTE PROCEDURE
add_cust();</font><br/><font size="2">> </font><br /><font size="2">> </font><br /><font size="2">> Someone
willingto evaluate this for me, and telling me if it is safe</font><br /><font size="2">> to use as it is? or things
Imay do wrong?</font><br /><font size="2">> </font><br /><font size="2">> TIA,</font><br /><font size="2">>
Michiel</font><br/><font size="2">> </font><br /><font size="2">> </font><br /><font size="2">Trigger functions
canonly return type OPAQUE which isn't seen by the client program.</font><br /><font size="2">To get the value of the
serialfield for the last insert do:</font><br /><font size="2">SELECT
currval('TableName_SerialFieldName_seq');</font><br/><font size="2">This will get the last value from the sequence used
bythis connection (although it will error if no values have been requested).</font><p><font size="2">hth,</font><br
/><fontsize="2">- Stuart</font>