From: Jeff Boes <jeff@endpoint.com>
To: pgsql-sql@postgresql.org
Subject: CREATE TABLE AS SELECT
Date: Fri, 29 Jul 2005 13:09:48 -0400
Message-ID: <42EA62DC.4060205@endpoint.com> (raw)
Offered up because I have no explanation, and curiosity overwhelms me:
I was attempting to create a table from a SELECT statement against
another table:
create table foo
as select
a,
f(b)
from xxx;
The function f() attempts to make a unique value based on its argument
(it's actually a "username" constructor, making "jboes" out of "Jeff
Boes"). The odd thing is that function f() also looks into the table
"foo" to see if the value it's constructing is truly unique; if it is
not, it tacks on a "1", "2", etc. until it gets a unique value.
The odd behavior is as follows: with a "CREATE TABLE ... AS SELECT"
statement, the function never found duplicate values, so I ended up with
f(a) = f(a') = f(a''), etc. I tried defining the function as STABLE,
then VOLATILE, without success. But if I changed to create the table
first, and then do "INSERT INTO ... SELECT", the function worked properly.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jeff@endpoint.com
Subject: Re: CREATE TABLE AS SELECT
In-Reply-To: <42EA62DC.4060205@endpoint.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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