Ordering of results in query templates involving UNION

Поиск
Список
Период
Сортировка
От Edmund Horner
Тема Ordering of results in query templates involving UNION
Дата
Msg-id CAMyN-kBD-RiZU9OeMT1u2q5Zg6mZiBCXbYNMBdaxQFjdzy9czw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Ordering of results in query templates involving UNION
Список pgadmin-hackers
Hi,

I was trying out the beta download and noticed that the SQL definition for some of my constraints had the columns in the wrong order.  I traced this to https://git.postgresql.org/gitweb/?p=pgadmin4.git;a=blob;f=web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/index_constraint/sql/get_constraint_cols.sql;hb=702609517f23be7c14e2f9d655f66ea8ac43dd9c which has the form of a SELECT ... UNION  SELECT ... for each column in the constraint.  I was able to fix this one case by appending the loop index as a new column and ordering by that:

    {% for n in range(colcnt|int) %}
    {% if loop.index != 1 %}
    UNION SELECT  pg_get_indexdef({{ cid|string }}, {{ loop.index|string }}, true) AS column, {{ loop.index|string }} AS idx
    {% else %}
    SELECT  pg_get_indexdef({{ cid|string }} , {{ loop.index|string }} , true) AS column, {{ loop.index|string }} AS idx
    {% endif %}
    {% endfor %}
    ORDER BY idx

There are a few other templates where it looks like the same pattern happens.  In a lot of them you use UNION ALL, which may coincidentally return rows the same order as the individual SELECT statements.  In the one above you use UNION so hashing is used and rows are returned in a less predictable order.  I think that even in the first case an ORDER BY clause is required for correctness -- or the rows need to be sorted in the client code before generating the SQL.

I have not attached a patch as I've not been involved on pgAdmin development.  But if the problem and the fix makes sense I would be happy to work on one to try to add ordering to UNION-based query templates (e.g. the ones on this list  https://git.postgresql.org/gitweb/?p=pgadmin4.git&a=search&h=HEAD&st=grep&s=UNION ).

Cheers,
Edmund Horner

В списке pgadmin-hackers по дате отправления: