pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtrying to learn plpqsql... so please forgive..
7+ messages / 5 participants
[nested] [flat]
* trying to learn plpqsql... so please forgive..
@ 2002-11-20 00:16 Michiel Lange <michiel@minas.demon.nl>
2002-11-20 09:44 ` Re: trying to learn plpqsql... so please forgive.. Luis Sousa <llsousa@ualg.pt>
2002-11-20 16:49 ` Re: trying to learn plpqsql... so please forgive.. Josh Berkus <josh@agliodbs.com>
2002-11-20 22:14 ` Re: trying to learn plpqsql... so please forgive.. Michiel Lange <michiel@minas.demon.nl>
0 siblings, 3 replies; 7+ messages in thread
From: Michiel Lange @ 2002-11-20 00:16 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: trying to learn plpqsql... so please forgive..
2002-11-20 00:16 trying to learn plpqsql... so please forgive.. Michiel Lange <michiel@minas.demon.nl>
@ 2002-11-20 09:44 ` Luis Sousa <llsousa@ualg.pt>
2 siblings, 0 replies; 7+ messages in thread
From: Luis Sousa @ 2002-11-20 09:44 UTC (permalink / raw)
To: Michiel Lange <michiel@minas.demon.nl>; +Cc: pgsql-sql
> 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
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.2K) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: trying to learn plpqsql... so please forgive..
2002-11-20 00:16 trying to learn plpqsql... so please forgive.. Michiel Lange <michiel@minas.demon.nl>
@ 2002-11-20 16:49 ` Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 7+ messages in thread
From: Josh Berkus @ 2002-11-20 16:49 UTC (permalink / raw)
To: Michiel Lange <michiel@minas.demon.nl>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: trying to learn plpqsql... so please forgive..
2002-11-20 00:16 trying to learn plpqsql... so please forgive.. Michiel Lange <michiel@minas.demon.nl>
@ 2002-11-20 22:14 ` Michiel Lange <michiel@minas.demon.nl>
2002-11-20 22:27 ` Re: trying to learn plpqsql... so please forgive.. Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 7+ messages in thread
From: Michiel Lange @ 2002-11-20 22:14 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; pgsql-sql; +Cc: Michiel Lange <michiel@minas.demon.nl>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: trying to learn plpqsql... so please forgive..
2002-11-20 00:16 trying to learn plpqsql... so please forgive.. Michiel Lange <michiel@minas.demon.nl>
2002-11-20 22:14 ` Re: trying to learn plpqsql... so please forgive.. Michiel Lange <michiel@minas.demon.nl>
@ 2002-11-20 22:27 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 7+ messages in thread
From: Josh Berkus @ 2002-11-20 22:27 UTC (permalink / raw)
To: Michiel Lange <michiel@minas.demon.nl>; pgsql-sql; +Cc: Michiel Lange <michiel@minas.demon.nl>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: trying to learn plpqsql... so please forgive..
@ 2002-11-20 09:48 Henshall, Stuart - Design & Print <SHenshall@westcountry-design-print.co.uk>
2002-11-20 09:58 ` Re: trying to learn plpqsql... so please forgive.. Johannes Lochmann <johannes.lochmann@chello.at>
0 siblings, 1 reply; 7+ messages in thread
From: Henshall, Stuart - Design & Print @ 2002-11-20 09:48 UTC (permalink / raw)
To: Michiel Lange <michiel@minas.demon.nl>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: trying to learn plpqsql... so please forgive..
2002-11-20 09:48 Re: trying to learn plpqsql... so please forgive.. Henshall, Stuart - Design & Print <SHenshall@westcountry-design-print.co.uk>
@ 2002-11-20 09:58 ` Johannes Lochmann <johannes.lochmann@chello.at>
0 siblings, 0 replies; 7+ messages in thread
From: Johannes Lochmann @ 2002-11-20 09:58 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2002-11-20 22:27 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-20 00:16 trying to learn plpqsql... so please forgive.. Michiel Lange <michiel@minas.demon.nl>
2002-11-20 09:44 ` Luis Sousa <llsousa@ualg.pt>
2002-11-20 16:49 ` Josh Berkus <josh@agliodbs.com>
2002-11-20 22:14 ` Michiel Lange <michiel@minas.demon.nl>
2002-11-20 22:27 ` Josh Berkus <josh@agliodbs.com>
2002-11-20 09:48 Re: trying to learn plpqsql... so please forgive.. Henshall, Stuart - Design & Print <SHenshall@westcountry-design-print.co.uk>
2002-11-20 09:58 ` Johannes Lochmann <johannes.lochmann@chello.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox