Michiel Lange wrote:
> Maybe this should be sent to novice... I was not certain, but if it
> should, please tell me so.
>
> The matter at hand is this:
>
> 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.
>
> CREATE FUNCTION add_cust() RETURNS INT4 AS ' -- SERIAL data type is
> really an INT4 (and some more).
> BEGIN
> RETURN NEW.p_key;
> END;
> ' LANGUAGE 'plpgsql';
>
> CREATE TRIGGER add_cust BEFORE INSERT ON table
> FOR EACH ROW EXECUTE PROCEDURE add_cust();
>
>
> Someone willing to evaluate this for me, and telling me if it is safe
> to use as it is? or things I may do wrong?
>
> TIA,
> Michiel
>
>
Trigger functions can only return type OPAQUE which isn't seen by the client
program.
To get the value of the serial field for the last insert do:
SELECT currval('TableName_SerialFieldName_seq');
This will get the last value from the sequence used by this connection
(although it will error if no values have been requested).
hth,
- Stuart