From: Michiel Lange <michiel@minas.demon.nl>
To: Josh Berkus <josh@agliodbs.com>
To: pgsql-sql@postgresql.org
Cc: Michiel Lange <michiel@minas.demon.nl>
Cc: pgsql-sql@postgresql.org
Subject: Re: trying to learn plpqsql... so please forgive..
Date: Wed, 20 Nov 2002 23:14:16 +0100
Message-ID: <5.1.0.14.0.20021120225235.00b71db8@192.168.1.3> (raw)
In-Reply-To: <web-1834137@davinci.ethosmedia.com>
References: <5.1.0.14.0.20021120010436.00b4c920@192.168.1.3>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: michiel@minas.demon.nl, josh@agliodbs.com
Subject: Re: trying to learn plpqsql... so please forgive..
In-Reply-To: <5.1.0.14.0.20021120225235.00b71db8@192.168.1.3>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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