Обсуждение: Shema functionality in 7.3
Can someone who has experience from the new 7.3 version tell me if SELECT tableA.foo FROM shema1.tableA, shema2.tableB WHERE tableA.bar = tableB.bar is possible thanks to the new schema functionality? And is it possible to grant users permissions on whole schemas? Queries like the one above is possible in Oracle, and I would really like to see this in PostgreSQL as well. (I guess we could call this feature "cross schema joins".) Note: I would not consider this as crossdb functionality, as tableA and tableB exist in the same database (using Oracle terms: the same instance), but in different shemas. --------------------------------------------------------- Mikael Carneholm, M.Sc. The University College of Boras, Sweden Email: mikael.carneholm@ida.utb.hb.se
Mikael Carneholm wrote: > Can someone who has experience from the new 7.3 version tell me > if > > SELECT tableA.foo > FROM shema1.tableA, shema2.tableB > WHERE tableA.bar = tableB.bar > > is possible thanks to the new schema functionality? Like this? CREATE SCHEMA schema1; CREATE SCHEMA schema2; CREATE TABLE schema1.table_a(bar int, foo text); INSERT INTO schema1.table_a VALUES(1,'a'); INSERT INTO schema1.table_a VALUES(2,'b'); CREATE TABLE schema2.table_b(bar int, foo text); INSERT INTO schema2.table_b VALUES(1,'c'); INSERT INTO schema2.table_b VALUES(2,'d'); regression=# SELECT t1.foo, t2.foo FROM schema1.table_a t1, schema2.table_b t2 WHERE t1.bar = t2.bar; foo | foo -----+----- a | c b | d (2 rows) > And is it possible to grant users permissions on whole schemas? Not sure exactly what you mean, but see: http://developer.postgresql.org/docs/postgres/sql-createschema.html http://developer.postgresql.org/docs/postgres/sql-grant.html > Queries like the one above is possible in Oracle, and I would really > like to see this in PostgreSQL as well. (I guess we could call this > feature "cross schema joins".) > > Note: I would not consider this as crossdb functionality, as tableA > and tableB exist in the same database (using Oracle terms: the > same instance), but in different shemas. > It's not the same as a cross db join, but the ability to use schemas is hoped to reduce the need for that. You should look at contrib/dblink if you want to join data across databases or servers. HTH, Joe
How the heck do you enable command editing (ie, up/down arrows, tab for table name completion, etc, etc) in psql on OS X (10.2.x)? I could never get it to work with 7.2 and it doesn't work with 7.3 either. :( I've installed the latest readline and tried about 10 million (give or take) different TERM settings. thanks! eric
"Eric B.Ridge" <ebr@tcdi.com> writes:
> How the heck do you enable command editing (ie, up/down arrows, tab for
> table name completion, etc, etc) in psql on OS X (10.2.x)? I could
> never get it to work with 7.2 and it doesn't work with 7.3 either. :(
> I've installed the latest readline and tried about 10 million (give or
> take) different TERM settings.
Should work if you reconfigure and rebuild PG after installing readline.
Keep an eye on the configure output to make sure it's finding readline.
regards, tom lane
On Sunday, December 1, 2002, at 09:39 PM, Tom Lane wrote: > "Eric B.Ridge" <ebr@tcdi.com> writes: >> How the heck do you enable command editing (ie, up/down arrows, tab >> for >> table name completion, etc, etc) in psql on OS X (10.2.x)? I could >> never get it to work with 7.2 and it doesn't work with 7.3 either. :( >> I've installed the latest readline and tried about 10 million (give or >> take) different TERM settings. > > Should work if you reconfigure and rebuild PG after installing > readline. > Keep an eye on the configure output to make sure it's finding readline. hehehe. I'm glad you pointed out the obvious. Although I did recompile/install PG after installing readline, I failed to do a "make clean" first. No wonder it didn't work. It does now. eric
> Like this? > > CREATE SCHEMA schema1; > CREATE SCHEMA schema2; > CREATE TABLE schema1.table_a(bar int, foo text); > INSERT INTO schema1.table_a VALUES(1,'a'); > INSERT INTO schema1.table_a VALUES(2,'b'); > CREATE TABLE schema2.table_b(bar int, foo text); > INSERT INTO schema2.table_b VALUES(1,'c'); > INSERT INTO schema2.table_b VALUES(2,'d'); > > regression=# SELECT t1.foo, t2.foo FROM schema1.table_a t1, > schema2.table_b t2 WHERE t1.bar = t2.bar; > foo | foo > -----+----- > a | c > b | d > (2 rows) SUPER! (Thanks to both Joe and Jochem who took the time to answer) Now, with both schema and prepared queries (stored procedures) support there is NOTHING LEFT of the Oracle functionality I used to miss in 7.2 (except perhaps tablespaces). Shema support is essential for logical grouping of tables and mapping to applications which, in extension, enhances reusability a great deal! (userB who owns schemaB and represents application B can now re-use schemaA.table1 without having to connect as userA & do the joining on the client side, provided of course that userB has been granted read rights on schemaA.table1) When I was working with Oracle, we had a 1:1 mapping between schema<->user/owner<- >application - this can now be reproduced in Pg as well! Kudos to the dev team, you have done a tremendous job! - Mikael --------------------------------------------------------- Mikael Carneholm, M.Sc. The University College of Boras, Sweden Email: mikael.carneholm@ida.utb.hb.se