-- Check the contents.jsgo field points to a valid URL.
-- This is triggered on rows inserted or updated in contents.
-- $Id: contents_jsgo_check_tg.sql,v 1.2 2006/07/26 13:41:34 rich Exp $

create or replace function contents_jsgo_check_tg() returns trigger as '

declare
	my_hostid integer;
	my_count integer;

begin
	if new.jsgo is not null then
		-- Get the hostid
		select into my_hostid p.hostid
		  from pages p
		 where p.id = new.pageid;
		-- Check that (hostid, url) where url = new.jsgo exists.
		select into my_count count(p.*)
		  from pages p
		 where p.hostid = my_hostid
                   and p.url is not null
		   and p.url = new.jsgo
                   and p.redirect is null;
		if my_count < 1 then
			raise exception ''contents.jsgo points to non-existent page (%, %)'',
			  my_hostid, new.jsgo;
		end if;
	end if;

	-- Everything is OK.  Return the new row unmodified.
	return new;
end;

' language plpgsql;