Re: Defining and Using variables in a postgres function

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

On 2/2/07, Alban Hertroys <alban(at)magproductions(dot)nl> wrote:
>
> 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(at)magproductions(dot)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 //
>