Thread: finding my schema (for sequences)

I am used to creating sequences separately and using them for default
values instead of using the serial type. I am not sure why... but that's the
way I have been doing it. Maybe I don't trust the generated names.
I should probably just get over it, but anyhow...

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


The problem is that unlike a comparable serial column, my default value
does not qualify the sequence with the right schema name.

Is there a way to do that, such that my schematest file does not need to
know which schema I am loading the file into?

Or should I just get over my aversion to serial?

_________________________________________________________________
Working moms: Find helpful tips here on managing kids, home, work �  and
yourself.   http://special.msn.com/msnbc/workingmom.armx

>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