Re: Error in ORDER BY on check constraints in psql - Mailing list pgsql-patches

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Everything is sorted by object name in \d <table> except check
> constraints for some reason.  It seems it's ordering by the wrong column.
> Seems like a bug to me.

That was probably done deliberately, back in the day when constraints
tended to have uselessly random names like "$1" --- sorting by the
constraint text was more helpful.  I agree that now sorting by name
seems like the better thing.

> Attached is the trivial patch.

I think there's nothing wrong with the "ORDER BY 1" part ... it's the
fact that the columns are selected in a different order than they'll
be used that seems bizarre to me.  I fixed it like this instead.

            regards, tom lane

*** src/bin/psql/describe.c.orig    Fri Oct 14 23:00:19 2005
--- src/bin/psql/describe.c    Thu Oct 20 01:09:29 2005
***************
*** 1036,1044 ****
          if (tableinfo.checks)
          {
              printfPQExpBuffer(&buf,
!                               "SELECT "
!                               "pg_catalog.pg_get_constraintdef(r.oid, true), "
!                               "conname\n"
                                "FROM pg_catalog.pg_constraint r\n"
                      "WHERE r.conrelid = '%s' AND r.contype = 'c' ORDER BY 1",
                                oid);
--- 1036,1043 ----
          if (tableinfo.checks)
          {
              printfPQExpBuffer(&buf,
!                               "SELECT r.conname, "
!                               "pg_catalog.pg_get_constraintdef(r.oid, true)\n"
                                "FROM pg_catalog.pg_constraint r\n"
                      "WHERE r.conrelid = '%s' AND r.contype = 'c' ORDER BY 1",
                                oid);
***************
*** 1192,1199 ****
              for (i = 0; i < check_count; i++)
              {
                  printfPQExpBuffer(&buf, _("    \"%s\" %s"),
!                                   PQgetvalue(result2, i, 1),
!                                   PQgetvalue(result2, i, 0));

                  footers[count_footers++] = pg_strdup(buf.data);
              }
--- 1191,1198 ----
              for (i = 0; i < check_count; i++)
              {
                  printfPQExpBuffer(&buf, _("    \"%s\" %s"),
!                                   PQgetvalue(result2, i, 0),
!                                   PQgetvalue(result2, i, 1));

                  footers[count_footers++] = pg_strdup(buf.data);
              }