From: | David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: ORDER BY in prepared statements |
Date: | 2015-01-21 21:20:06 |
Message-ID: | 1421875206968-5834948.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Bryn Jeffries | 2015-01-21 22:38:25 | Re: ORDER BY in prepared statements |
Previous Message | Adrian Klaver | 2015-01-21 21:18:14 | Re: ORDER BY in prepared statements |