Re: [HACKERS] placeholder syntax
Personally, I agree. The '?' sucks for multiple reasons. The major reason being when you want to use the same parameter in more than one place in a statement. Another reason is query rewrites where you have to reorganize the actual order of parameters. You are then forced to first convert the '?' into some other form (like the $1, $2 syntax that PostgreSQL uses today). But even if it sucks, it's used by a very broad range of clients. As Greg mentions, both ODBC and JDBC uses this syntax and no other SQL database that I know of treats '?' as an operator. The '?' is, and will remain, a parameter placeholder in SQL for most people. So even if '?' shouldn't be emulated at this time, perhaps it would be a good idea to abandon it as a valid operator? Kind regards, Thomas Hallgren "Greg Stark" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > > Tom Lane <[EMAIL PROTECTED]> writes: > > > Abhijit Menon-Sen <[EMAIL PROTECTED]> writes: > > > Should Postgres accept ? as a placeholder? > > > > In short, I think this notation sucks and I don't want to emulate it. > > Certainly it sucks. Unfortunately it's the supported ODBC API which is > emulated by everyone else, including JDBC and DBI. So the world's pretty much > stuck with it. > > However this isn't Postgres's problem. If you want to write code that works > with multiple databases then you're going to want to be using something like > ODBC or JDBC or DBI anyways. In which case it's the driver's responsibility to > provide the standard API which includes translating ? into appropriate syntax > for the database. > > In other words, your problem should already be solved by your driver. > > -- > greg > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 3: 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
Re: [HACKERS] placeholder syntax
Greg Stark wrote: Tom Lane <[EMAIL PROTECTED]> writes: Abhijit Menon-Sen <[EMAIL PROTECTED]> writes: Should Postgres accept ? as a placeholder? In short, I think this notation sucks and I don't want to emulate it. Certainly it sucks. Unfortunately it's the supported ODBC API which is emulated by everyone else, including JDBC and DBI. So the world's pretty much stuck with it. However this isn't Postgres's problem. If you want to write code that works with multiple databases then you're going to want to be using something like ODBC or JDBC or DBI anyways. In which case it's the driver's responsibility to provide the standard API which includes translating ? into appropriate syntax for the database. This brings back memories. This is how the whole Access hack for the parse-time transformation of '= NULL' to 'IS NULL' came about. IIRC, older versions of Access would invoke SQLPrepare() with a statement like: SELECT * FROM employees WHERE employeeid = ? then invoke SQLBindParameter() with NULL as the value, followed by SQLExecute() and the backend would receive: SELECT * FROM employees WHERE employeeid = NULL Later versions of one of the Access components (jet, mdac, access.exe - who knows where) changed its behavior and never performed similarly... Mike Mascari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] placeholder syntax
Tom Lane <[EMAIL PROTECTED]> writes: > Abhijit Menon-Sen <[EMAIL PROTECTED]> writes: > > Should Postgres accept ? as a placeholder? > > In short, I think this notation sucks and I don't want to emulate it. Certainly it sucks. Unfortunately it's the supported ODBC API which is emulated by everyone else, including JDBC and DBI. So the world's pretty much stuck with it. However this isn't Postgres's problem. If you want to write code that works with multiple databases then you're going to want to be using something like ODBC or JDBC or DBI anyways. In which case it's the driver's responsibility to provide the standard API which includes translating ? into appropriate syntax for the database. In other words, your problem should already be solved by your driver. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] placeholder syntax
Abhijit Menon-Sen <[EMAIL PROTECTED]> writes: > Should Postgres accept ? as a placeholder? We think it's an operator character: regression=# select 1 ? 4; ERROR: operator does not exist: integer ? integer I count eighteen standard operators that would be broken if we changed '?' to mean a parameter. I am also pretty unclear on why '?' is a good notation for parameters, seeing that it is very hard for either the user or the machine to tell which is which when there are multiple parameters. In short, I think this notation sucks and I don't want to emulate it. We do have the :foo notation in ecpg, which may be your closest parallel for handling Oracle-workalike code anyway. regards, tom lane ---(end of broadcast)--- TIP 3: 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
[HACKERS] placeholder syntax
PostgreSQL currently uses $1/$2 for placeholders in prepared statements. I'm writing something that may potentially submit queries to both Oracle and Postgres, and it seems Oracle doesn't accept this syntax. Someone on IRC said I could use ? for both Oracle and Postgres. It isn't entirely clear to me if Oracle accepts it, but Postgres doesn't seem to. My copy of the SQL92 standard says: «In SQL-statements that are executed dynamically, the parameters are called dynamic parameters (s) and are represented in SQL language by a (?).» (There's also an "" production in the standard, which looks like the :foo syntax that Oracle also accepts, but I'm not sure it applies to placeholders. The standard is a bit hard to read.) Should Postgres accept ? as a placeholder? (If so, I'll dig around and try to figure out how to make it do so.) -- ams ---(end of broadcast)--- TIP 8: explain analyze is your friend
