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
