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

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

From
Gunnar Lindholm
Date:
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.

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

From
Tod McQuillin
Date:
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




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

From
"Simeo Reig"
Date:
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
>


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

From
Gunnar Lindholm
Date:
> 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';

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

From
Martijn van Oosterhout
Date:
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.

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

From
Gunnar Lindholm
Date:
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....


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

From
"Command Prompt, Inc."
Date:
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


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

From
Gunnar Lindholm
Date:
> 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