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

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

> 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. 


Try this:

SELECT currval(<sequence name from table>);

Luis Sousa

<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> 

On Wednesday 20 November 2002 10:48, Henshall, Stuart - Design & Print wrote:
> Michiel Lange wrote:
> Trigger functions can only return type OPAQUE which isn't seen by the
> client program.

I just saw that in 7.3 the return type for triggers has changed to TRIGGER.

HTH

Johannes Lochmann

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 INSERTtrigger can modify the inserted data, or reject it,
orupdate 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

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        

Michiel,

> 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)

No, you can't return a value to the client from a Trigger.   Not ever.
Triggers modify data, and they can log stuff, but they can't return values to
the calling interface.

Now, what you could do is replace the whole insert with a function, doing:

SELECT add_cust( name, address, phone, credit_card);

Which does the inserting and returns the new id to the client.    This is a
solution I frequently use in my web apps.

-Josh Berkus