Thread: how do the pro's do this? (still a newbie)
Hello.
As a newbie in the stored procedure programming area I would like to know how
you could do the following with a function in plpgsql.
(It's a theoretical question, so do not suggest changing the tables :-)
I have
table A :
id integer primary key default nextval('something'),
dohA integer
table B:
rid integer references A,
dohB integer
and I wish to create a function "foo( dohA, dohB)" that inserts the values
dohA and dohB into the proper tables A and B and the reference in table B
should of course be connected to the PK in table A. You understand what I
mean, right?
Now, how do I write
function foo(integer, integer)
begin work
... please fill this space with some code.... !!!!
commit
Is there some realy good tutorial on this, please tell me so?
TIA, Gunnar.
On Tue, 30 Oct 2001, Gunnar Lindholm wrote:
> table A :
> id integer primary key default nextval('something'),
> dohA integer
>
> table B:
> rid integer references A,
> dohB integer
>
> and I wish to create a function "foo( dohA, dohB)" that inserts the values
> dohA and dohB into the proper tables A and B and the reference in table B
> should of course be connected to the PK in table A.
Something like this should work. I did not test it at all though.
CREATE FUNCTION foo(integer, integer) RETURNS integer AS '
DECLARE
a_id A.id%TYPE;
BEGIN
a_id := nextval(''something'');
INSERT INTO A VALUES (a_id, $1);
INSERT INTO B VALUES (a_id, $2);
RETURN a_id;
END;
' LANGUAGE 'plpgsql';
I am not sure about starting new transactions inside plpgsql functions.
Since postgresql doesn't support nested transactions yet I think you may
have to begin and end the transaction outside of the function, like so:
BEGIN TRANSACTION;
SELECT foo(1, 2);
COMMIT;
I hope someone will correct me if I am wrong on this point.
--
Tod McQuillin
try this http://www.brasileiro.net/postgres/plpgsql/ -- Simeó Reig ----- Original Message ----- From: "Gunnar Lindholm" <gunnar@gunix.mine.nu> To: <pgsql-general@postgresql.org> Sent: Tuesday, October 30, 2001 1:11 PM Subject: [GENERAL] how do the pro's do this? (still a newbie) > Hello. > As a newbie in the stored procedure programming area I would like to know how > you could do the following with a function in plpgsql. > (It's a theoretical question, so do not suggest changing the tables :-) > I have > > table A : > id integer primary key default nextval('something'), > dohA integer > > table B: > rid integer references A, > dohB integer > > and I wish to create a function "foo( dohA, dohB)" that inserts the values > dohA and dohB into the proper tables A and B and the reference in table B > should of course be connected to the PK in table A. You understand what I > mean, right? > > Now, how do I write > > function foo(integer, integer) > begin work > ... please fill this space with some code.... !!!! > commit > > Is there some realy good tutorial on this, please tell me so? > TIA, Gunnar. > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
> On Tue, 30 Oct 2001, Gunnar Lindholm wrote:
> > table A :
> > id integer primary key default nextval('something'),
> > dohA integer
> >
> > table B:
> > rid integer references A,
> > dohB integer
> >
> Something like this should work. I did not test it at all though.
>
> CREATE FUNCTION foo(integer, integer) RETURNS integer AS '
> DECLARE
> a_id A.id%TYPE;
> BEGIN
> a_id := nextval(''something'');
> INSERT INTO A VALUES (a_id, $1);
> INSERT INTO B VALUES (a_id, $2);
> RETURN a_id;
> END;
> ' LANGUAGE 'plpgsql';
I wrote a function similar to this and when inserting
select foo('/new.html','194.165.2.24','2001-Oct-28 10:34:25+01');
it complains that
ERROR: Attribute 'vad_seq' not found
"vad_seq" is a sequence just like "something" is in the example above.
The code I wrote is written below... So if anybody can tell me what's wrong,
please do so. I find the error message very strange. I can't believe that it
can not find the sequence.... Shouldn't the sequence be accessible from every
function in that database?
TIA
Gunnar.
Feel free to comment on my code since I've just started learning.
-----------------
create sequence vad_seq;
create sequence vem_seq;
create table vadt(
id integer primary key,
vad varchar(500) UNIQUE
);
create table vemt(
id integer primary key,
vem cidr UNIQUE
);
create table visit(
nar timestamp,
vem integer references vemt on delete cascade,
vad integer references vadt on delete cascade,
UNIQUE (nar, vem, vad)
);
create function foo(varchar(500),cidr,timestamp)
returns integer as 'declare
Xvad ALIAS FOR $1;
Xvem ALIAS FOR $2;
Xnar ALIAS FOR $3;
tmpsel_rec record;
ivad integer;
ivem integer;
BEGIN
-- get the vad id
SELECT INTO tmpsel_rec id
FROM vadt
WHERE vad = Xvad;
IF FOUND
THEN
ivad := tmpsel_rec.id;
ELSE
ivad := nextval("vad_seq");
INSERT INTO vadt
VALUES (ivad,"Xvad");
END IF;
-- get the vem id
SELECT INTO tmpsel_rec id
FROM vemt
WHERE vem = Xvem;
IF FOUND
THEN
ivem := tmpsel_rec.id;
ELSE
ivem := nextval("vem_seq");
INSERT INTO vemt
VALUES (ivem,"Xvem");
END IF;
INSERT INTO visit VALUES
($Xnar, ivem, ivad);
END;'
LANGUAGE 'plpgsql';
On Sat, Nov 03, 2001 at 08:34:37AM +0100, Gunnar Lindholm wrote:
> I wrote a function similar to this and when inserting
> select foo('/new.html','194.165.2.24','2001-Oct-28 10:34:25+01');
> it complains that
> ERROR: Attribute 'vad_seq' not found
> "vad_seq" is a sequence just like "something" is in the example above.
> The code I wrote is written below... So if anybody can tell me what's wrong,
> please do so. I find the error message very strange. I can't believe that it
> can not find the sequence.... Shouldn't the sequence be accessible from every
> function in that database?
Well, my only suggestion is:
> ivad := nextval("vad_seq");
^^^^^^^^^
Should the name be in single quotes?
HTH,
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> Magnetism, electricity and motion are like a three-for-two special offer:
> if you have two of them, the third one comes free.
On Saturday 03 November 2001 09:32, you wrote:
> On Sat, Nov 03, 2001 at 08:34:37AM +0100, Gunnar Lindholm wrote:
> > I wrote a function similar to this and when inserting
> > select foo('/new.html','194.165.2.24','2001-Oct-28 10:34:25+01');
> > it complains that
> > ERROR: Attribute 'vad_seq' not found
> > "vad_seq" is a sequence just like "something" is in the example above.
> > The code I wrote is written below... So if anybody can tell me what's
> > wrong, please do so. I find the error message very strange. I can't
> > believe that it can not find the sequence.... Shouldn't the sequence be
> > accessible from every function in that database?
>
> Well, my only suggestion is:
> > ivad := nextval("vad_seq");
>
> ^^^^^^^^^
>
> Should the name be in single quotes?
Then I get this error...
ERROR: parser: parse error at or near "vad_seq"
so there is something I've done wrong, but I can't see....
On Sat, 3 Nov 2001, Gunnar Lindholm wrote:
>>Well, my only suggestion is:
>>> ivad := nextval("vad_seq");
>> ^^^^^^^^^
>>Should the name be in single quotes?
>Then I get this error...
>ERROR: parser: parse error at or near "vad_seq"
>so there is something I've done wrong, but I can't see....
Are you escaping the single quotes properly? Remember that your PL/pgSQL
function definition is itself bound by single quotes, so inside the code
definition for CREATE FUNCTION that line should look like:
ivad := nextval(''vad_seq'');
or even:
ivad := nextval(\'vad_seq\');
Regards,
Jw.
--
jlx@commandprompt.com
by way of pgsql-general@commandprompt.com
> Are you escaping the single quotes properly? Remember that your PL/pgSQL
> function definition is itself bound by single quotes, so inside the code
> definition for CREATE FUNCTION that line should look like:
>
> ivad := nextval(''vad_seq'');
> or even:
> ivad := nextval(\'vad_seq\');
Thanks, that worked.
Gunnar.
----
gunix.mine.nu - always under destruction