Re: finding my schema (for sequences)

>Let's say I am writing a file for creating a database. Say ...
>
>-- schematest
>CREATE SEQUENCE foo_id_seq;
>CREATE TABLE foo(
> id integer
> PRIMARY KEY
> DEFAULT nextval('foo_id_seq'),
> xx text
>);
>
>
>I want to be able to ...
>
>lee=# drop schema beta cascade;
>DROP SCHEMA
>lee=# create schema beta;
>CREATE SCHEMA
>lee=# set search_path to beta;
>SET
>lee=# \i schematest
>CREATE SEQUENCE
>CREATE TABLE
>

Hmm. Just in case it is not clear why I want to do this ... after the
above, if I reconnect and fail to set my search_path, the DEFAULT
fails ...

lee=# insert into beta.foo (xx) values ('one');
ERROR: relation "foo_id_seq" does not exist

I Found the current_schema() function, but my naive attempt ...

CREATE SEQUENCE foo_id_seq;
CREATE TABLE foo(
id integer
PRIMARY KEY
DEFAULT nextval(current_schema()||'.foo_id_seq'),
xx text
);

creates a DEFAULT that looks up the current_schema() at run time
instead of when creating the table. Any hints?

_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus