Re: Defining and Using variables in a postgres function

[Alban Hertroys <alban@xxxxxxxxxxxxxxxxx>]

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@xxxxxxxxxxxxxxxxx

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 //