Re: No return from trigger function

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