pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing schema
2+ messages / 2 participants
[nested] [flat]
* Using schema
@ 2007-11-30 15:00 Daniel Caune <daniel.caune@ubisoft.com>
0 siblings, 1 reply; 2+ messages in thread
From: Daniel Caune @ 2007-11-30 15:00 UTC (permalink / raw)
To: pgsql-sql
Hi,
I tried to figure out how to select a particular schema before executing
statement on its objects without having to specify the schema in these
statements (cf. the instruction USE supported in some other RDBMS)
PostgreSQL allows modifying dynamically the schema search path:
SET search_path TO my_schema, public;
http://www.postgresql.org/docs/current/static/ddl-schemas.html
http://www.postgresql.org/docs/current/static/sql-set.html
By doing so, the creation statements will be executed in the schema
my_schema.
SET search_path TO public;
CREATE TABLE foo(i int);
SET search_path TO my_schema, public;
CREATE TABLE foo(i int);
List of relations
Schema | Name | Type | Owner
-----------+------------------+-------------
public | bar | table | dbo_ubilive
my_schema | foo | table | dbo_ubilive
The cool thing with this, compared to the USE statement supported by
some other RDBMS, is that the user is not restricted to one given schema
without explicit schema declaration:
SELECT * FROM foo; -- Uses schema my_schema
SELECT * FROM bar; -- Uses schema public
SELECT * FROM foo, bar WHERE foo.i = bar.i; -- Uses both schemas
That is damn flexible! :-)
--
Daniel
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Using schema
@ 2007-12-01 03:33 Scott Marlowe <scott.marlowe@gmail.com>
parent: Daniel Caune <daniel.caune@ubisoft.com>
0 siblings, 0 replies; 2+ messages in thread
From: Scott Marlowe @ 2007-12-01 03:33 UTC (permalink / raw)
To: Daniel Caune <daniel.caune@ubisoft.com>; +Cc: pgsql-sql
On Nov 30, 2007 9:00 AM, Daniel Caune <daniel.caune@ubisoft.com> wrote:
> The cool thing with this, compared to the USE statement supported by
> some other RDBMS, is that the user is not restricted to one given schema
> without explicit schema declaration:
>
> SELECT * FROM foo; -- Uses schema my_schema
> SELECT * FROM bar; -- Uses schema public
> SELECT * FROM foo, bar WHERE foo.i = bar.i; -- Uses both schemas
>
> That is damn flexible! :-)
Hehe. yeah, every time I have to use Oracle at work I feel like I'm
putting on a straight jacket.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-12-01 03:33 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-30 15:00 Using schema Daniel Caune <daniel.caune@ubisoft.com>
2007-12-01 03:33 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox