Re: Defining and Using variables in a postgres function - Mailing list pgsql-general

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)