Re: Explicite typecasting of functions

On Wednesday 14 Aug 2002 10:30 am, Andreas Tille wrote:
> Hello,
>
> I want to insert new data into a table with increasing data set ids.
> The table has two separate "regions" of data: Those with Ids below
> 1000000 and other.  If I want to create a new Id in the "lower region"
> I tried the following (simplified example):
>
>
> CREATE TABLE Items (
>   Id    int DEFAULT NextItem()

> CREATE FUNCTION NextItem() RETURNS INT4
>     AS 'select max(Id)+1 from Items where Id < 1000000;'
>     LANGUAGE 'sql';

>    ERROR:  Relation "items" does not exist
>
> Any hint to solve this kind of chicken-egg-problem?

Your solution is not safe anyway - you could end up with two processes trying
to insert the next value.

Can I suggest two sequences: item_low_seq and item_high_seq? Set the initial
value of each to 1,000,000 and 99,000,000 (or whatever) and then use
whichever sequence is appropriate.

In the example above you'd want something like: id int not null default nextval('item_low_seq')

- Richard Huxton