-- Classified Ads Package
--
-- @author Deds Castillo (deds@infiniteinfo.com)
-- @creation-date 2002-10-08
-- @cvs-id $Id: ads-package-create.sql,v 1.1 2003/07/16 04:52:31 rmello Exp $
--

select define_function_args('ca_ad__delete','ad_id');

create or replace function ca_ad__delete (integer)
returns integer as '
declare
    p_ad_id         alias for $1;
    rec             record;
begin

-- delete any associated generic attribute values
    delete from ca_attribute_values
    where object_id = p_ad_id;

-- set our latest and live revisions to null
    update cr_items
    set live_revision = null,
        latest_revision = null
    where item_id = p_ad_id;
 
-- delete entry from the ads table
    for rec in
        select revision_id
        from cr_revisions
        where item_id = p_ad_id

    LOOP
        delete from cr_ca_ads
        where ad_id = rec.revision_id;
    end LOOP;

    return 0;
end;
' language 'plpgsql';