Thread: 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 (<dynamic parameter
specification>s)and   are represented in SQL language by a <question mark> (?).»
 

(There's also an "<embedded variable name>" 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

Abhijit Menon-Sen <ams@wiw.org> 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

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Abhijit Menon-Sen <ams@wiw.org> 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

Greg Stark wrote:

> Tom Lane <tgl@sss.pgh.pa.us> writes:
> 
>>Abhijit Menon-Sen <ams@wiw.org> 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

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" <gsstark@mit.edu> wrote in message
news:87brjcstpu.fsf@stark.xeocode.com...
>
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>
> > Abhijit Menon-Sen <ams@wiw.org> 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 majordomo@postgresql.org)
>