No return from trigger function

From: "James B(dot) Byrne" <byrnejb(at)harte-lyne(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: No return from trigger function
Date: 2009-04-08 19:59:22
Message-ID: 53451.216.185.71.24.1239220762.squirrel@webmail.harte-lyne.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


I just need another set of eyes to see whatever it is that I am
overlooking.

This is the function:

CREATE OR REPLACE FUNCTION hll_pg_fn_ident_insert()
RETURNS TRIGGER AS $pg_fn$
-- ROW AFTER TRIGGER
-- trigger passes identifier_type and _description
-- received as ARGV[0] and ARGV[1]
BEGIN
INSERT INTO identifiers(
entity_id,
identifier_type,
identifier_value,
identifier_description,
changed_at,
changed_by,
created_at,
created_by,
effective_from)
VALUES(
NEW.id,
TG_ARGV[0],
NEW.entity_common_name,
TG_ARGV[1],
current_timestamp,
'trigger',
current_timestamp,
'trigger',
current_timestamp);

-- Assume the INSERT fails because of a unique key violation,
-- (identifier_type + identifier_value + entity_id)
--
-- This does not matter since we only need ensure that this
-- alias exists, so handle the exception and return:
EXCEPTION
WHEN unique_violation THEN
-- NULL -- do nothing

RETURN NULL; -- AFTER trigger results are ignored anyway
END;
$pg_fn$ LANGUAGE plpgsql;

This is the trigger:

CREATE TRIGGER hll_pg_tr_entity_identifier_akna
AFTER INSERT OR UPDATE ON entities
FOR EACH ROW EXECUTE PROCEDURE hll_pg_fn_ident_insert(
"AKNA", "Common Name auto-insert");

I am getting this error:

PGError: ERROR: control reached end of trigger procedure
without RETURN
CONTEXT: PL/pgSQL function "hll_pg_fn_ident_insert"
: INSERT INTO "entities" ("entity_legal_name",
"entity_legal_name_key", "changed_by", "entity_common_name",
"created_by", "lock_version", "changed_at",
"entity_legal_form", "created_at") VALUES(E'My Entity Legal
Name', E'myentitylegalname', E'not available', E'my entity',
E'not available', 0, '2009-04-08 19:46:49', E'PERS',
'2009-04-08 19:46:49.446650')

Help??

--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB(at)Harte-Lyne(dot)ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3

Responses

Browse pgsql-general by date

  From Date Subject
Next Message hubert depesz lubaczewski 2009-04-08 20:05:29 Re: No return from trigger function
Previous Message Ian Mayo 2009-04-08 19:30:28 Re: Are there performance advantages in storing bulky field in separate table?