Search Postgresql Archives

Re: ORDER BY with exception

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 6/21/07, brian <brian@xxxxxxxxxxxxxxxx> wrote:
I have a lookup table with a bunch of disciplines:

# SELECT id, name FROM discipline;
  id |        name
----+---------------------
   1 | writing
   2 | visual arts
   3 | music
   4 | dance
   5 | film and television
   6 | theatre
   7 | media arts
   8 | community
   9 | fine craft
  10 | other
(10 rows)

and a function that returns each discipline name along with the total
number of records in another table (showcase) that are related to each
discipline. Each showcase entry may have 0 or more items (showcase_item)
related to it, so ones that have no items are disregarded here. Also,
only showcases that have been accepted should be counted.

First, here's the working function:

CREATE FUNCTION getshowcasetotalsbydiscipline(OUT name text, OUT total
integer) RETURNS SETOF record
AS $$

DECLARE
  rec record;

BEGIN
  FOR rec IN
   EXECUTE 'SELECT id, name, 1 AS total FROM discipline'
   LOOP
     name := rec.name;

     SELECT INTO rec.total

       -- a showcase may be in the DB but not accepted by an admin
       SUM(CASE s.accepted WHEN TRUE THEN 1 ELSE 0 END)
       FROM showcase AS s
       WHERE s.id IN

         -- a showcase may exist with no items, so should be ignored
         (SELECT si.showcase_id FROM showcase_item AS si
           WHERE si.discipline_id = rec.id);

          -- If no showcase items have this discipline,
          -- give it a total of zero

     IF rec.total IS NULL THEN
       SELECT INTO total 0;
     ELSE
       total := rec.total;
     END IF;

   RETURN NEXT;
   END LOOP;

   RETURN;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

test=# SELECT * FROM getShowcaseTotalsByDiscipline();
         name         | total
---------------------+-------
  writing             |    130
  visual arts         |    252
  music               |    458
  dance               |    131
  film and television |    102
  theatre             |    271
  media arts          |     83
  community           |     20
  fine craft          |     78
  other               |     59
(10 rows)

Works fine, but i'd like to order the disciplines alphabetically
*except* have 'other' fall at the end. So, should i loop a second time,
after summing the totals, and keep the 'other' row aside, then add it to
the end?

(btw, the output of this function is cached until a new showcase is
accepted)

Or, should i re-order the disciplines alphabetically in the lookup
trable, keeping 'other' to be last?

I could do the latter, although it would mean a fair bit of work because
the disciplines table relates to a bunch of other stuff, as well. Also,
there's always the chance that a new discipline will be added in the
future. I suppose i could write a trigger that bumped the 'other' id
above that of the new entry, then re-relate everything else in the DB
that's connected to the 'other' discipline. But that strikes me as kind
of a hack.

The third option is to re-order the resultset in the PHP script that
displays this. But that wasn't why i chose Postgres for this app ;-)

brian

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster


It seems to me you could replace it all with one query, something like this:

SELECT discipline, COUNT(1) FROM showcase WHERE EXISTS (SELECT * FROM
showcase_item WHERE showcase_id = showcase.id LIMIT 1) GROUP BY
discipline ORDER BY (discipline != 'other'), discipline;

- Josh


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux