Re: BUG #6774: FOR IN SELECT LOOP ignores ORDER BY

On 7/30/2012 5:56 AM, Boris Folgmann wrote:
> Hi,
>
> hubert depesz lubaczewski schrieb/wrote:
>> generally - order by datname is understood as "order by *variable
>> datname*". - which is null.
>
> It's clear that it's a shadowing problem. But it's not a "FOR IN EXECUTE"
> where a variable makes sense. I mean why is a "ORDER BY variable" valid in
> "FOR IN"? In SQL you can't use ORDER BY NULL. If the order by clause is not
> correct the whole statement should raise an error.

Note that PL/pgSQL replaces all local variables inside a query with
$-parameters for the prepared SPI plan. The parser rejects ordering by
non-integer constants, but it does not reject ordering by $-parameters
or constant expressions. (maybe it should). You can for example

SELECT * FROM something ORDER BY 'foo'||'bar';

Jan

--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin