how do the pro's do this? (still a newbie)

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

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/

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