Re: No return from trigger function - Mailing list pgsql-general

From Guillaume Lelarge
Subject Re: No return from trigger function
Date
Msg-id 200904082210.03480.guillaume@lelarge.info
Whole thread Raw
In response to No return from trigger function  ("James B. Byrne" <byrnejb@harte-lyne.ca>)
List pgsql-general
Le mercredi 08 avril 2009 à 21:59:22, James B. Byrne a écrit :
> 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??
>

The RETURN NULL is in the exception block. You need to put one before this
block.


--
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

pgsql-general by date:

Previous
From: Kashmir
Date:
Subject: some external sql not working in psql
Next
From: Tom Lane
Date:
Subject: Re: No return from trigger function