-- Create functions for monolith discussion widget.
-- Copyright (C) 2002 Richard W.M. Jones <rich@annexia.org>
-- This code is NOT REDISTRIBUTABLE. To use this widget you must purchase
-- a license at http://www.annexia.org/
--
-- $Id: ml_discussion_functions.sql,v 1.1 2003/02/22 12:49:21 rich Exp $

-- Function to count the number of unread articles in the given
-- newsgroup.

create or replace function ml_discussion_unread (integer, integer)
	returns integer as '

declare
  my_userid alias for $1;
  my_resid alias for $2;
  rec record;
  stmt text;

begin

  stmt := ''select count (id) as result
              from ml_discussion_article
             where resid = '' || my_resid || ''
                   and not (false'';

  for rec in select low, high from ml_discussion_read
              where resid = my_resid and userid = my_userid
  loop
    stmt := stmt || '' or (id between '' || rec.low ||
                    '' and '' || (rec.high-1) || '')'';
  end loop;

  stmt := stmt || '')'';

  for rec in execute stmt
  loop
    return rec.result;
  end loop;

end;

' language plpgsql;
