pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedExplicite typecasting of functions
6+ messages / 3 participants
[nested] [flat]
* Explicite typecasting of functions
@ 2002-08-14 09:30 Andreas Tille <tillea@rki.de>
2002-08-14 11:10 ` Re: Explicite typecasting of functions Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andreas Tille @ 2002-08-14 09:30 UTC (permalink / raw)
To: pgsql-sql
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 exist
Unable to identify a function that satisfies the given argument types
You may 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Explicite typecasting of functions
2002-08-14 09:30 Explicite typecasting of functions Andreas Tille <tillea@rki.de>
@ 2002-08-14 11:10 ` Richard Huxton <dev@archonet.com>
2002-08-14 15:20 ` Re: Explicite typecasting of functions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2002-08-14 11:10 UTC (permalink / raw)
To: Andreas Tille <tillea@rki.de>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Explicite typecasting of functions
2002-08-14 09:30 Explicite typecasting of functions Andreas Tille <tillea@rki.de>
2002-08-14 11:10 ` Re: Explicite typecasting of functions Richard Huxton <dev@archonet.com>
@ 2002-08-14 15:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-08-15 07:08 ` Re: Explicite typecasting of functions Andreas Tille <tillea@rki.de>
2002-08-15 07:13 ` Re: Explicite typecasting of functions Andreas Tille <tillea@rki.de>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2002-08-14 15:20 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Andreas Tille <tillea@rki.de>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Explicite typecasting of functions
2002-08-14 09:30 Explicite typecasting of functions Andreas Tille <tillea@rki.de>
2002-08-14 11:10 ` Re: Explicite typecasting of functions Richard Huxton <dev@archonet.com>
2002-08-14 15:20 ` Re: Explicite typecasting of functions Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-08-15 07:08 ` Andreas Tille <tillea@rki.de>
1 sibling, 0 replies; 6+ messages in thread
From: Andreas Tille @ 2002-08-15 07:08 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Explicite typecasting of functions
2002-08-14 09:30 Explicite typecasting of functions Andreas Tille <tillea@rki.de>
2002-08-14 11:10 ` Re: Explicite typecasting of functions Richard Huxton <dev@archonet.com>
2002-08-14 15:20 ` Re: Explicite typecasting of functions Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-08-15 07:13 ` Andreas Tille <tillea@rki.de>
2002-08-15 08:43 ` Re: Explicite typecasting of functions Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 6+ messages in thread
From: Andreas Tille @ 2002-08-15 07:13 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Explicite typecasting of functions
2002-08-14 09:30 Explicite typecasting of functions Andreas Tille <tillea@rki.de>
2002-08-14 11:10 ` Re: Explicite typecasting of functions Richard Huxton <dev@archonet.com>
2002-08-14 15:20 ` Re: Explicite typecasting of functions Tom Lane <tgl@sss.pgh.pa.us>
2002-08-15 07:13 ` Re: Explicite typecasting of functions Andreas Tille <tillea@rki.de>
@ 2002-08-15 08:43 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2002-08-15 08:43 UTC (permalink / raw)
To: Andreas Tille <tillea@rki.de>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2002-08-15 08:43 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-14 09:30 Explicite typecasting of functions Andreas Tille <tillea@rki.de>
2002-08-14 11:10 ` Richard Huxton <dev@archonet.com>
2002-08-14 15:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-08-15 07:08 ` Andreas Tille <tillea@rki.de>
2002-08-15 07:13 ` Andreas Tille <tillea@rki.de>
2002-08-15 08:43 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox