Message-ID: <42EA62DC.4060205@endpoint.com>
Date: Fri, 29 Jul 2005 13:09:48 -0400
From: Jeff Boes <jeff@endpoint.com>
Organization: End Point
User-Agent: Mozilla Thunderbird 1.0.5 (Windows/20050711)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: CREATE TABLE AS SELECT
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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.