CREATE VIEW "roster" as
  SELECT
    pres."jid", pres."resource", pres."type", pres."show", pres."status",
    pres."priority", pres."music_title", pres."music_state"
    FROM presences pres
    WHERE
      (
        pres."type" = 'available' AND
        pres."priority" = (
          SELECT max(presences."priority") AS max
            FROM presences
            WHERE
              presences."jid" = pres."jid" AND
              presences."type" = 'available'
          )
        AND
        pres.id = (
          SELECT max(presences.id) AS max
            FROM presences
            WHERE
              presences."jid" = pres.jid AND
              presences."priority" = pres.priority AND
              presences."type" = 'available'
        )
      ) OR (
        pres."type" = 'unavailable' AND
        pres.id = (
          SELECT max(presences.id) AS max
            FROM presences
            WHERE (presences.jid = pres.jid)
        )
        AND
        pres.jid <> ALL (
          SELECT presences.jid
            FROM presences
            WHERE (presences."type" = 'available')
        )
      )
  UNION
  SELECT
    people.jid, '', 'unavailable', '', 'No presence has been received',
    0, '', ''
    FROM people
    WHERE
      people.jid <> ALL (
        SELECT presences.jid
        FROM presences
        WHERE
          presences."type" = 'available' OR
          presences."type" = 'unavailable'
      )
;