Re: 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
> >
> 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';