CREATE SEQUENCE asset_seq;
CREATE TABLE assets (
asset_id integer not null default nextval('asset_seq'),
desk text not null,
status text not null,
priority text not null,
deadline date not null
);
CREATE TABLE documentasset (
doc_id integer not null unique,
label text not null
) INHERITS (assets);
CREATE TABLE mediaasset (
media_id integer not null unique
) INHERITS (assets);
####
/* From your command prompt type
createlang plpgsql
It generates the following code automatically so it
isn't nessessary to remember this next bit
*/
CREATE FUNCTION "plpgsql_call_handler" ()
RETURNS opaque AS '/usr/local/lib/plpgsql.so',
'plpgsql_call_handler' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER "plpgsql_call_handler"
LANCOMPILER 'PL/pgSQL';
##
##
CREATE FUNCTION asset_id_ins_upd () RETURNS OPAQUE AS '
DECLARE
found INTEGER;
BEGIN
IF TG_OP = ''UPDATE'' THEN
-- Return OK for unchanging asset_id on
-- update operations
IF NEW.asset_id = OLD.asset_id THEN
RETURN NEW;
END IF;
END IF;
SELECT 1 INTO found
FROM assets
WHERE assets.asset_id = NEW.asset_id;
IF FOUND THEN
RAISE EXCEPTION
END IF;
END;
' LANGUAGE 'plpgsql' WITH (isstrict);
CREATE TRIGGER assets_asset_id_ins_upd BEFORE INSERT OR UPDATE ON assets
FOR EACH ROW EXECUTE PROCEDURE asset_id_ins_upd();