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