By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,514 Members | 794 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,514 IT Pros & Developers. It's quick & easy.

Before trigger question

P: n/a
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

Nov 11 '05 #1
Share this Question
Share on Google+
2 Replies

P: n/a
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

Nov 11 '05 #2

P: n/a
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

Nov 11 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.