Re: [GENERAL] finding my schema (for sequences)

[Lee Harr]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html