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

От:

Johannes Lochmann <johannes.lochmann@chello.at>

Дата:

20 ноября 2002 г. в 04:57:21

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

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

От:

Josh Berkus <josh@agliodbs.com>

Дата:

20 ноября 2002 г. в 17:25:41


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

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

От:

Luis Sousa <llsousa@ualg.pt>

Дата:

20 ноября 2002 г. в 04:45:16

> When I do an 'INSERT INTO  VALUES '
> 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();

Luis Sousa

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

От:

Michiel Lange <michiel@minas.demon.nl>

Дата:

19 ноября 2002 г. в 19:16:13

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

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

От:

Michiel Lange <michiel@minas.demon.nl>

Дата:

20 ноября 2002 г. в 17:14:21

To those who pointed at the SELECT currval , 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  VALUES '
> > 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

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

От:

"Henshall, Stuart - Design & Print" <SHenshall@westcountry-design-print.co.uk>

Дата:

20 ноября 2002 г. в 04:50:53

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

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

От:

"Josh Berkus" <josh@agliodbs.com>

Дата:

20 ноября 2002 г. в 11:45:42

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  VALUES '
> 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, 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