From: | Adriaan Joubert <a(dot)joubert(at)albourne(dot)com> |
---|---|
To: | Andy Lewis <alewis(at)themecca(dot)net> |
Cc: | pgsql-general(at)postgreSQL(dot)org |
Subject: | Re: [GENERAL] Trigger or Rule? |
Date: | 1999-04-26 13:22:08 |
Message-ID: | 37246880.559350E4@albourne.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adriaan Joubert | 1999-04-26 13:33:24 | Re: [GENERAL] Trigger or Rule? |
Previous Message | Andy Lewis | 1999-04-26 12:59:18 | Trigger or Rule? |