ptt-gossip sql patch

ptt-gossip-patch.sql

CREATE INDEX object_title ON object(cname);
CLUSTER object using object_title;
----------------------------------------
CREATE INDEX vd_ptt_post_origin ON vd_ptt_post(origin);
CLUSTER vd_ptt_post using vd_ptt_post_origin;
----------------------------------------
CREATE INDEX vd_ptt_post_oid ON vd_ptt_post(oid);
CLUSTER vd_ptt_post using vd_ptt_post_oid;
----------------------------------------
CREATE INDEX vd_ptt_post_count ON vd_ptt_post(count);
CLUSTER vd_ptt_post using vd_ptt_post_count;
----------------------------------------
CREATE OR REPLACE FUNCTION origin_post(_oid int,_title varchar) RETURNS int AS
$BODY$ 
declare _reloid int;
begin 
	--if not exists(select regexp_matches(_title, '^Re: |^Fw: ')) then return null; end if;
	select oid from object where type=6
	and cname=regexp_replace(_title, '^Re: |^Fw: ', '') into _reloid;
	if _reloid is null then _reloid:=_oid; end if;
	return _reloid;
end; 
$BODY$
  LANGUAGE plpgsql;
----------------------------------------

CREATE MATERIALIZED VIEW vd_ptt_post AS 
 SELECT o.oid,
    o.cname,
    o.cdes,
    o.ename,
    o.since,
    url.hostname::text || url.path::text AS url,
    origin_post(oid,cname) origin,
    ( SELECT count(*) AS count
           FROM object o1,
            orel oo
          WHERE o1.oid = oo.oid1 AND o1.oid = o.oid) AS count,
          
    ( SELECT (( SELECT count(*) AS count
                   FROM object o1,
                    orel oo
                  WHERE o1.oid = oo.oid1 AND o1.oid = o.oid)) / log(2::numeric, (date_part('day'::text, now() - o.since::timestamp with time zone)::integer + 2)::numeric)) AS weight
   FROM object o,
    url
  WHERE o.type = 6 AND o.nclick = 1 AND o.oid = url.uid
WITH DATA;

----------------------------------------
create MATERIALIZED view vd_ptt_group_post as
select p.oid,p.cname title,p.since,p.count,p.origin,p.url,p.ename ownername,w.max 
from vd_ptt_post p,(select origin oid ,max(weight) max 
from vd_ptt_post group by origin order by max(weight) desc limit 10)as w 
where w.oid=p.origin order by w.max desc;