All pastes #503077 Raw Edit

Something

public text v1 · immutable
·published 2007-05-22 18:47 UTC
rendered paste body
CREATE OR REPLACE FUNCTION f_search_all(text, text, text, int, int)
RETURNS setof t_search AS $$ 
	SELECT  DISTINCT(dr.id), 
					doc.name,
					doc.description,
					dt.mime_type, 
					dt.type_name,
					dt.image_url,
					dr.document_id, 
					dr.revision_external_id,
					dr.user_id_committed,
					dr.filesize,
					doc.keywords,
					c.content_text,
					rank(c.fti_idx, q) AS rank_fti, 
					rank(doc.keywords_idx, q) AS rank_keywords, 
					rank(doc.name_idx, q) AS rank_name,
					rank(doc.description_idx, q) AS rank_description,
					(rank(c.fti_idx, q) 
						+ rank(doc.keywords_idx, q) 
						+ rank(doc.name_idx, q) 
						+ rank(doc.description_idx, q) ) AS rank_total,
					dr.last_change, 
					headline(c.content_text, q, 'StartSel=$2, StopSel=$3, MinWords=$4, MaxWords=$5'), 
					headline(doc.keywords, q, 'StartSel=$2, StopSel=$3, MinWords=$4, MaxWords=$5'), 
					headline(doc.name, q, 'StartSel=$2, StopSel=$3, MinWords=$4, MaxWords=$5'), 
					headline(doc.description, q, 'StartSel=$2, StopSel=$3, MinWords=$4, MaxWords=$5')
	FROM 
		documents doc,
		document_revisions dr, 
		content c, 
		document_types dt,
		to_tsquery($1) AS q
	WHERE 
		(c.fti_idx @@ q 
		OR doc.keywords_idx @@ q
		OR doc.name_idx @@ q
		OR doc.description_idx @@ q)
		AND c.revision_id=dr.id
		AND doc.id=dr.document_id
		AND doc.document_type_id=dt.id
	ORDER BY rank_total DESC;
$$ LANGUAGE sql;