Re: [SQL] RULES on SELECT with JDBC/perlDBI from other RDBMS products?
Bath, David wrote: Folks, I'm looking at using pg to be the main platform for integrating info from other RDBMS products (particularly Oracle) as pg seems to be the most flexible RDBMS around. Disregarding writing to foreign products, query-plan efficiencies, or differences of SQL dialect, I'd like to have a way of setting up a fairly-transparent SELECT within pg that pulls rows from the other product. I wonder if anyone has attempted something like this, and can recommend (or even deprecate) an approach, and perhaps point to a code template. Are you familiar with the dblink() module in the contrib/ directory of the source distribution? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to delete Large Object from Database?
Premsun Choltanwanich wrote: Dear All, I use '$libdir/lo' for manage my PostgreSQL Large Object. It work fine for me to get and put Large Object from and to database. However I found something that may not correct when I try to backup my data. It seem that I cannot delete Large Object from database. It seem the thing I can do is only delete the reference oid from table but Object still in database. So,How to delete Large Object from Database? or Is my understanding wrong? Deleting the OID does not remove the object itself - see details of lo_unlink() in Chapter 28 of the manuals. I seem to recall some other utilities in the contrib/ directory of the source distribution too. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] RULES on SELECT with JDBC/perlDBI from other RDBMS
On 10/6/05 9:07 PM, "Bath, David" <[EMAIL PROTECTED]> wrote: > Folks, > > I'm looking at using pg to be the main platform for integrating info > from other RDBMS products (particularly Oracle) as pg seems to be > the most flexible RDBMS around. > > Disregarding writing to foreign products, query-plan efficiencies, > or differences of SQL dialect, I'd like to have a way of setting > up a fairly-transparent SELECT within pg that pulls rows from the > other product. I wonder if anyone has attempted something like > this, and can recommend (or even deprecate) an approach, and perhaps > point to a code template. > > Possible approaches that occur to me include > 1. For the low-level integration > a) Use of "foreign" JDBC thin client within PL/Java > b) Use of untrusted perl DBI/DBD > c) Use of low-level C code (e.g. declaring Oracle OCI calls > to pg) - very labor intensive > 2. For "transparent" use by other routines > Create pg table/view, then write rules that use functions > returning rows (including barf exceptions if someone tries > writing to a table). > > If I can embed a java thin client binary/jar for the foreign > database in pg and use it using pg pl/java, then I'd like to > go that path as it would decrease setup/admin effort when > porting to other platforms, as there would be few dependencies > on things like external perl modules. > > If any pg developer gurus are reading this, perhaps such templates > might be worthwhile including in the contrib bundle? > The DBI-link project is quite useful for doing just what you describe, if I understand you correctly. http://pgfoundry.org/projects/dbi-link/ http://www.pervasivepostgres.com/postgresql/tidbits_June05.asp Sean ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] using pg_tables and tablename in queries
Thanks for the help everyone. I got everything working. The query I was orignally looking for was something like this: SELECT * from mytable WHERE pg_tables.tablename IN ( select tablename from pg_tables where tablename like 'mytable _%' ) It turns out that isn't what I really wanted. I now have a nice stored procedure, with a clean interface. Thanks for all the help! On 10/5/05, codeWarrior <[EMAIL PROTECTED]> wrote: > > "Tom Lane" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > > solarsail <[EMAIL PROTECTED]> writes: > >> I have a large number of tables with a common naming convention > > > >> mytable001, mytable002, mytable003 ... mytable00n > > > >> I would like to do a query across all of the tables, however I do not > >> know > >> all of the tables before hand, and I do not want to ( cant ) manually > >> generate a query like > > > >> select * from mytable001, mytable002, mytable003 > > > >> I have a query that returns the names of the tables I want to query: > > > >> select tablename from pg_tables where tablename like 'mytable%' > > > > This looks to me like a situation in which you should rethink your > > data design. Those tables should all get merged into one big table, > > adding one extra column that reflects what you had been using to > > segregate the data into different tables. > > > > regards, tom lane > > Sounds like a classic opportunity to use the postgreSQL inheritance model to > me ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
