Thread: ORDER BY in prepared statements

> In a number of places on the web I've seen it claimed that ordering can be
> set via prepared statements.
> ...
> sandbox=# PREPARE testplan(text) AS
> SELECT * FROM test ORDER BY $1;
>
> But the output is not what one would expect:
>
> sandbox=# EXECUTE testplan('gender');
> ...
> As opposed to:
> sandbox=# SELECT * FROM test ORDER BY gender;

Your prepared statement version is actually comparable to this SQL:

    SELECT * FROM test ORDER BY 'gender'

which is effectually ordering by random.

I'm not sure how to make a prepared statement that lets you name a
column when you execute it. Maybe someone else can chime in if that's
possible.

Paul


--
_________________________________
Pulchritudo splendor veritatis.

On 01/21/2015 12:51 PM, Bryn Jeffries wrote:
> In a number of places on the web I've seen it claimed that ordering can
> be set via prepared statements.

Can you give a link to one of those examples?

>
> Many thanks,
>
> Bryn


--
Adrian Klaver
adrian.klaver@aklaver.com

Paul Jungwirth wrote
>> In a number of places on the web I've seen it claimed that ordering can
>> be
>> set via prepared statements.
>> ...
>> sandbox=# PREPARE testplan(text) AS
>> SELECT * FROM test ORDER BY $1;
>>
>> But the output is not what one would expect:
>>
>> sandbox=# EXECUTE testplan('gender');
>> ...
>> As opposed to:
>> sandbox=# SELECT * FROM test ORDER BY gender;
>
> Your prepared statement version is actually comparable to this SQL:
>
>     SELECT * FROM test ORDER BY 'gender'
>
> which is effectually ordering by random.
>
> I'm not sure how to make a prepared statement that lets you name a
> column when you execute it. Maybe someone else can chime in if that's
> possible.
>
> Paul

You cannot.  By definition parameters, in this context, are values - not
identifiers.  Queries with variable identifiers are called "dynamic SQL" and
can only be realized via the EXECUTE statement in pl/pgsql.  Yes, same name
different behavior because it is a different language.

http://www.postgresql.org/docs/9.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

It too has a "prepare" capability (USING) that is also limited to data
values and not identifiers.  Basically what this gives you is an
easy-to-access language and structure (i.e., function) to execute dynamic
SQL.  You can accomplish the same thing in whatever language and client
library you are using by creating a dynamic SQL statement to pass to SQL
PREPARE.

In both situations there is no way for the planner to plan and cache a
single query whose order by column varies.  No matter what you do at best
you can have a single plan for each explicit order by column that you wish
to specify.

David J.







--
View this message in context: http://postgresql.nabble.com/ORDER-BY-in-prepared-statements-tp5834944p5834948.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Paul Jungwirth wrote
> I'm not sure how to make a prepared statement that lets you name a
> column when you execute it. Maybe someone else can chime in if that's
> possible.

David J. responded
> You cannot.  By definition parameters, in this context, are values - not
> identifiers.
> [...]
> In both situations there is no way for the planner to plan and cache a
> single query whose order by column varies.  No matter what you do at best
> you can have a single plan for each explicit order by column that you wish
> to specify.

That's what I'd figured. The motivation to use prepared statements in
application layers is not so much having a single plan but more the
insulation from SQL injection. The intent of the given ORDER BY example was
to restricts inputs to valid identifiers rather than part of the query
expression.

Maybe what we need in ODBC libs and the like is a "protected
statement" that follows the same construction as a prepared statement but
additionally checks catalogs to validate identifiers.

Bryn

On 01/21/2015 03:09 PM, Bryn Jeffries wrote:
> Paul Jungwirth wrote
>> I'm not sure how to make a prepared statement that lets you name a
>> column when you execute it. Maybe someone else can chime in if that's
>> possible.
>
> David J. responded
>> You cannot.  By definition parameters, in this context, are values - not
>> identifiers.
>> [...]
>> In both situations there is no way for the planner to plan and cache a
>> single query whose order by column varies.  No matter what you do at best
>> you can have a single plan for each explicit order by column that you wish
>> to specify.
>
> That's what I'd figured. The motivation to use prepared statements in
> application layers is not so much having a single plan but more the
> insulation from SQL injection. The intent of the given ORDER BY example was
> to restricts inputs to valid identifiers rather than part of the query
> expression.


In addition to what David said, applications/frameworks may provide that
functionality. For example in Django:

https://docs.djangoproject.com/en/1.7/ref/models/querysets/#order-by


>
> Maybe what we need in ODBC libs and the like is a "protected
> statement" that follows the same construction as a prepared statement but
> additionally checks catalogs to validate identifiers.
>
> Bryn
>


--
Adrian Klaver
adrian.klaver@aklaver.com