Re: [GENERAL] Trigger or Rule?

Andy Lewis wrote:
>
> I have a table that among other things has a name, address, city, state
> fields. When I insert into, I want to be able to make sure that there is
> no duplicate records or that a row is inserted that is already in the DB.
>
> Question number one is: Should I use a trigger or a rule?
>
> And request number two is perhaps a sample that could get me started.
>
> I've read thru the Documentation and Man pages and tried creating a rule
> but, had no luck.
>

I know this isn't exactly what you want. I had a unique trigger in C,
but doing it in PL is much easier. Here is an example of a singleton --
i.e. a trigger that allows only one row in a table.


DROP FUNCTION singleton();
CREATE FUNCTION singleton () RETURNS opaque AS
'
DECLARE
BEGIN
   DELETE FROM daemon;
   RETURN new;
END;' LANGUAGE 'plpgsql';
DROP TRIGGER daemon_singleton ON daemon;
CREATE TRIGGER daemon_singleton BEFORE INSERT ON daemon
FOR EACH ROW EXECUTE PROCEDURE singleton('daemon');

The new row is always available in the variable 'new', so that you could
do something along the lines of

select count(*) into cnt from <tablename> where new.<key> = key;
if (cnt>0) then
    delete from <tablename> where key = new.<key>
end if

Remember to return new, leave spaces around the = comparisons, and
declare the variable cnt in the declare section (as int4 or something).

The documentation for PL is actually quite good, and you should also
have a look at the examples. You need to load PL as an interpreted
language, so you need something along the lines of

DROP FUNCTION plpgsql_call_handler();
CREATE FUNCTION plpgsql_call_handler() RETURNS opaque
        AS '<path to postgres>/lib/plpgsql.so'
        LANGUAGE 'C';

DROP PROCEDURAL LANGUAGE 'plpgsql';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
        HANDLER plpgsql_call_handler
        LANCOMPILER 'PL/pgSQL';

first.

Good luck, Adriaan