Re: how do the pro's do this? (still a newbie) - Mailing list pgsql-general

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