[BUGS] BUG #6774: FOR IN SELECT LOOP ignores ORDER BY

[boris]

The following bug has been logged on the website:

Bug reference:      6774
Logged by:          Boris Folgmann
Email address:      bo...@folgmann.de
PostgreSQL version: 8.4.12
Operating system:   CentOS 6.3
Description:        

This is an really interesting one!
I've trimmed down the problem so you can simply reproduce it by copy &
paste:

CREATE OR REPLACE FUNCTION ignores_order_by()
RETURNS TABLE(datname VARCHAR) AS $$
DECLARE
        r       RECORD;
BEGIN
        FOR r IN SELECT * FROM pg_database WHERE datallowconn ORDER BY
datname
        LOOP
            datname := r.datname;
            RETURN NEXT;
        END LOOP;
END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION respects_order_by()
RETURNS TABLE(dn VARCHAR) AS $$
DECLARE
        r       RECORD;
BEGIN
        FOR r IN SELECT * FROM pg_database WHERE datallowconn ORDER BY
datname
        LOOP
            dn := r.datname;
            RETURN NEXT;
        END LOOP;
END;
$$ LANGUAGE 'plpgsql';

SELECT * from ignores_order_by();
SELECT * from respects_order_by();

Now compare the different output!
The only difference of the two functions is that the first one uses a
variable with the same name of a column.
This might be a feature and not a bug, but browsing through the
documentation I could not find any documented restrictions on variable names
in this context.



-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs