Обсуждение: Explicite typecasting of functions

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()
) ;


/*  ERROR:  Function 'nextitem()' does not existUnable to identify a function that satisfies the given argument
typesYoumay need to add explicit typecasts*/
 

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


I did not found any trace of documentation how to do an explicit typecast
for the function.  Defining the function first fails because:
  ERROR:  Relation "items" does not exist

Any hint to solve this kind of chicken-egg-problem?

Kind regards
      Andreas.

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

Richard Huxton <dev@archonet.com> writes:
> On Wednesday 14 Aug 2002 10:30 am, Andreas Tille wrote:
>> 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.

I concur with Richard that Andreas needs to solve a different problem,
but just for the record, the way you could do it is
CREATE TABLE without mentioning the default
CREATE FUNCTION
ALTER TABLE ... SET DEFAULT

Note however that pg_dump is not bright enough to deduce that you did
this.  It will dump the table definition first, with the DEFAULT clause,
and so you'll have to do manual surgery on the dump file if you ever
need to reload.
        regards, tom lane

On Wed, 14 Aug 2002 Richard Huxton wrote:

> > 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.
While you are perfectly right in principle I can be sure that this can not
happen in this application.

> 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')
In fact I want to let PostgreSQL manage only the Ids of the 'lower region'
via sequences/functions whatever.  The 'higher region' is imported from
an external source and contains explicite Ids.

But anyway for academic reasons: What means adding 'explicit typecast'
of a func???
I was not able to find this term in the docs.

Kind regards
        Andreas.

On Wed, 14 Aug 2002, Tom Lane wrote:

> I concur with Richard that Andreas needs to solve a different problem,
> but just for the record, the way you could do it is
>
>     CREATE TABLE without mentioning the default
>
>     CREATE FUNCTION
>
>     ALTER TABLE ... SET DEFAULT
>
> Note however that pg_dump is not bright enough to deduce that you did
> this.  It will dump the table definition first, with the DEFAULT clause,
> and so you'll have to do manual surgery on the dump file if you ever
> need to reload.
Hmmm, the original problem I wanted to solve using this function is that
pg_dump is not bright enough to store sequences.  I use a development
machine to build the database do a pg_dump and after this I try to
put this dump to the production (readonly) server.  This procedure
every time requires manual setting of the sequences.  I tried to avoid
this by the function.  Any better way to do this?

Kind regards
        Andreas.

On Thursday 15 Aug 2002 8:13 am, Andreas Tille wrote:
> On Wed, 14 Aug 2002, Tom Lane wrote:
> >     CREATE TABLE without mentioning the default
> >     CREATE FUNCTION
> >     ALTER TABLE ... SET DEFAULT
> >
> > Note however that pg_dump is not bright enough to deduce that you did
> > this.  It will dump the table definition first, with the DEFAULT clause,
> > and so you'll have to do manual surgery on the dump file if you ever
> > need to reload.
>
> Hmmm, the original problem I wanted to solve using this function is that
> pg_dump is not bright enough to store sequences.

Eh? If you run a pg_dump on your database you should get something like:

CREATE SEQUENCE "bar_a_seq" start 1 increment 1 maxvalue 9223372036854775807
minvalue 1 cache 1;

CREATE TABLE "bar" (       "a" integer DEFAULT nextval('"bar_a_seq"'::text) NOT NULL,       "b" integer
);

... data then follows...

-- Name: bar_a_seq Type: SEQUENCE SET Owner: richardh
SELECT setval ('"bar_a_seq"', 3, true);

It's that last bit that sets the value of the sequence. Now AFAIK pg_dump's
been like that pretty much forever.

Note - if you only pg_dump the table, you won't get the sequence, you need to
dump the whole DB and grep away the bits you don't want.

- Richard Huxton