Thread: Defining and Using variables in a postgres function

I have a function like the follwoing:

CREATE OR REPLACE FUNCTION sp_insert_raw_email(bool, text, text, text, int4,text,text,text,text,text,text,text,timestamp)
  RETURNS void AS
$BODY$
BEGIN
-- SELECT STATEMENT GOES HERE--
INSERT INTO tbl_email(option_public,
  agency , id)
VALUES ($1,$2) ;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


For inserting the id, i need to query a table xyz, fetch the maximum id in it, increment it by 1 and store it in tbl_email.
Right after BEGIN in my function I have a commnet where in I need to query the xyz table, fetch the max id and store it in a variable and then I can increment this variable and store it in tbl_email.

How should i define this variable first and how to push the result of the query fired on table xyz.

Thanks in advance,
~Harpreet

Harpreet Dhaliwal wrote:
> I have a function like the follwoing:
>
> CREATE OR REPLACE FUNCTION sp_insert_raw_email(bool, text, text, text,
> int4,text,text,text,text,text,text,text,timestamp)
>  RETURNS void AS
> $BODY$
> BEGIN
> -- SELECT STATEMENT GOES HERE--
> INSERT INTO tbl_email(option_public,
>  agency , id)
> VALUES ($1,$2) ;
> END;
> $BODY$
>  LANGUAGE 'plpgsql' VOLATILE;
>
>
> For inserting the id, i need to query a table xyz, fetch the maximum id in
> it, increment it by 1 and store it in tbl_email.

Shouldn't you circumvent the whole concurrency mess you're getting
yourself into by using a sequence?

You're in trouble if this function gets called concurrently from
different sessions, unless you lock the relevant records. They'll both
see the same MAX(id) and try to insert records with the same id values.

> How should i define this variable first and how to push the result of the
> query fired on table xyz.

Yes indeed, like this:

DECLARE
   x int;
BEGIN
   SELECT INTO x MAX(id) + 1 FROM xyz;
   INSERT INTO tbl_email(option_public, agency , id)
       VALUES ($1,$2, x) ;

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

About the concurrency control, if i have both Select Max(id) and insert (id) in the same function, then would it be
a nice idea to put both these statements in the same function or differenct functions and then put the insert in a transaction and lock the table for any further query till insert commits.

Also, should i go with a table level lock or a row level lock in this scenario?
Thanks
~Harpreet

Harpreet Dhaliwal wrote:
> About the concurrency control, if i have both Select Max(id) and insert
> (id)

You know, if you don't top-post you don't need to tell me what you're
talking about ;)

> in the same function, then would it be
> a nice idea to put both these statements in the same function or differenct
> functions and then put the insert in a transaction and lock the table for
> any further query till insert commits.

I just realized you can do this in one statement - that wouldn't solve
the concurrency problem, though.

You can do this:
INSERT INTO tbl_email (option_public, agency, id)
    SELECT $1, $2, MAX(id) + 1
    FROM xyz;

> Also, should i go with a table level lock or a row level lock in this
> scenario?

I just realize you don't so much need a lock, you need a serialized
transaction. I can't say I know a lot about locks, I usually prevent
needing them.

Thinking of which... If there is _any_ relation between the MAX(id)
you're selecting from xyz and the record you insert into tbl_email, how
do you guarantee that you're actually looking at the correct record in xyz?
Basically the problem occurs when a new record was inserted and
committed into xyz while you were processing the insert of the previous
one. MAX(id) would return the id of the newly inserted record, _after_
you started processing.

I can't imagine what purpose the MAX(id) from a different table would
serve. If there isn't a relation between those fields, what benefit does
your method have over a sequence?

Regards,
--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

On Feb 1, 2007, at 12:09 PM, Harpreet Dhaliwal wrote:
> For inserting the id, i need to query a table xyz, fetch the
> maximum id in it, increment it by 1 and store it in tbl_email.
> Right after BEGIN in my function I have a commnet where in I need
> to query the xyz table, fetch the max id and store it in a variable
> and then I can increment this variable and store it in tbl_email.

That's not safe unless you get an exclusive lock on the table before
the select.

Why don't you just use a sequence/serial?

> How should i define this variable first and how to push the result
> of the query fired on table xyz.

SELECT INTO v_max_id
    max(id)
    FROM table
;
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

On Fri, Feb 02, 2007 at 17:18:39 +0100,
  Alban Hertroys <alban@magproductions.nl> wrote:
>
> You can do this:
> INSERT INTO tbl_email (option_public, agency, id)
>     SELECT $1, $2, MAX(id) + 1
>     FROM xyz;
>
> I just realize you don't so much need a lock, you need a serialized
> transaction. I can't say I know a lot about locks, I usually prevent
> needing them.

No, a serialized transaction isn't good enough. You need predicate locking,
which postgres doesn't have. So you need to use lock table to do effectively
the same thing with a more blunt instrument.