0

Postgres doesn't have the USE directive, unlike SQL Server or MySQL. There is the \c <database> command, but that only works in the psql REPL. Is there an alternative to USE that can be used with cfquery in Postgres? We would be using this with CF 11.

RHPT
  • 2,560
  • 5
  • 31
  • 43
  • 3
    Hmmm... why are you using the "USE" command in your cf queries - do you use it to switch between DBs? You could of course switch the "datasource" attribute out using a variable and accomplish the same thing no? – Mark A Kruger Dec 15 '15 at 15:40
  • There were choices made in the beginning of this app that was .... unfortunate. One result was that we have many, many databases and someone who is not me does not want to setup a different datasource for each one. – RHPT Dec 15 '15 at 15:50
  • Yeah - totally with you on that :) Not a posgres guy Randy - but I'll ask my team.. maybe someone knows. – Mark A Kruger Dec 15 '15 at 15:57
  • 1
    Unfortunately, you cannot change the database after the connection has been made (at least, not with the main bindings, including the official `libpq`; `psql`'s `\c` command will create a new connection behind the scenes). But, there is a (somewhat) similar concept, which may fill your needs: create multiple [schemas](http://www.postgresql.org/docs/current/static/ddl-schemas.html), instead of multiple databases. Then, you can switch between them with the [`SET search_path ...` command](http://www.postgresql.org/docs/current/static/sql-set.html). (In MySQL, schemas and databases are synonyms). – pozs Dec 15 '15 at 16:03
  • that sounds promising Pozs - though it means Randy will likely have to pick his poison - ether create a bunch of schemas or create a bunch of DSNs. – Mark A Kruger Dec 15 '15 at 16:44
  • The CF Admin API might ease the DSN creation. – RHPT Dec 15 '15 at 17:02
  • I didn't realize other methods were available for creating datasources. – Dan Bracuk Dec 15 '15 at 17:11
  • Here's an example using mySQL http://swalsh.org/blog/2013/03/20/programmatically-creating-new-datasource-in-coldfusion/ and the official Adobe docs http://help.adobe.com/en_US/ColdFusion/10.0/Admin/WSc3ff6d0ea77859461172e0811cbf364104-7fcf.html – RHPT Dec 16 '15 at 15:43

0 Answers0