Re: ORDER BY in prepared statements

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.