Re: CREATE TABLE AS SELECT

JB> Offered up because I have no explanation, and curiosity overwhelms me:

JB> I was attempting to create a table from a SELECT statement against
JB> another table:

JB> create table foo
JB> as select
JB> a,
JB> f(b)
JB> from xxx;

In this command table foo populated after the select statement
sucessfully finished. The function always runs on an empty foo table.

JB> The function f() attempts to make a unique value based on its argument
JB> (it's actually a "username" constructor, making "jboes" out of "Jeff
JB> Boes"). The odd thing is that function f() also looks into the table
JB> "foo" to see if the value it's constructing is truly unique; if it is
JB> not, it tacks on a "1", "2", etc. until it gets a unique value.

JB> The odd behavior is as follows: with a "CREATE TABLE ... AS SELECT"
JB> statement, the function never found duplicate values, so I ended up with
JB> f(a) = f(a') = f(a''), etc. I tried defining the function as STABLE,
JB> then VOLATILE, without success. But if I changed to create the table
JB> first, and then do "INSERT INTO ... SELECT", the function worked properly.