Before trigger question

[Jean-Christian Imbeault]

Following up on the discussion yesterday on how to prevent the
generation on log error messages when someone tries to insert a
duplicate primary key record and Nigel Andrew's suggestion of using a
BEFORE trigger I have written the following function.

I am pretty new to triggers and function so I would appreciate and
comments or suggestions. Is this function sufficient? Can it be made
more efficient?

create or replace function pot_doc_dup_check() returns trigger as '
DECLARE pkExists BOOLEAN := false;
BEGIN
SELECT INTO pkExists EXISTS (SELECT null FROM pot_documents where
data_id=NEW.data_id);
IF pkExists = true THEN
RETURN null;
ELSE
RETURN NEW;
END IF;
END;
' LANGUAGE 'plpgsql' with (iscachable);

create trigger pot_doc_dup_check BEFORE INSERT ON pot_documents
for each ROW EXECUTE PROCEDURE pot_doc_dup_check();

TIA,

Jean-Christian Imbeault
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Alvaro Herrera]

On Thu, Jul 03, 2003 at 09:28:25AM +0900, Jean-Christian Imbeault wrote:

create or replace function pot_doc_dup_check() returns trigger as '
DECLARE pkExists BOOLEAN := false;
BEGIN
SELECT INTO pkExists EXISTS (SELECT null FROM pot_documents where
data_id=NEW.data_id);
IF pkExists = true THEN
RETURN null;
ELSE
RETURN NEW;
END IF;
END;
' LANGUAGE 'plpgsql' with (iscachable);

Well, I dunno if it's more efficient, but *I think* you can write it as

BEGIN
SELECT 1 FROM pot_documents WHERE data_id=NEW.data_id;
IF FOUND THEN
RETURN null;
ELSE
RETURN NEW;
END IF;
END;

(untested)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"In fact, the basic problem with Perl 5's subroutines is that they're not
crufty enough, so the cruft leaks out into user-defined code instead, by
the Conservation of Cruft Principle." (Larry Wall, Apocalypse 6)

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Jean-Christian Imbeault]

Alvaro Herrera wrote:

Well, I dunno if it's more efficient, but *I think* you can write it as

SELECT 1 FROM pot_documents WHERE data_id=NEW.data_id;
Would a 'LIMIT 1' be useful here? But data_id is defined as a primary
key so maybe pg is smart enough to know there can only be one match and
will stop looking after it finds the first match?
(untested)

Testing now :) But would I test for speed?

Jean-Christian Imbeault
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings