Thread: No return from trigger function
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@Harte-Lyne.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
On Wed, Apr 08, 2009 at 03:59:22PM -0400, James B. Byrne wrote: > EXCEPTION > WHEN unique_violation THEN > -- NULL -- do nothing > > RETURN NULL; -- AFTER trigger results are ignored anyway > END; exception is part of begin/exception/end; block so you will need one more begin and one more end; Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
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
"James B. Byrne" <byrnejb@harte-lyne.ca> writes:
> I just need another set of eyes to see whatever it is that I am
> overlooking.
The RETURN is inside the EXCEPTION clause.
You really need two BEGINs here, one for the outer function body and
one for the exception block around the INSERT.
regards, tom lane
On Wed, April 8, 2009 16:05, hubert depesz lubaczewski wrote:
> On Wed, Apr 08, 2009 at 03:59:22PM -0400, James B. Byrne wrote:
>> EXCEPTION
>> WHEN unique_violation THEN
>> -- NULL -- do nothing
>>
>> RETURN NULL; -- AFTER trigger results are ignored anyway
>> END;
>
> exception is part of begin/exception/end; block
> so you will need one more begin and one more end;
>
Does this mean that the example given on pg. 798 of the manual is in
error, or have I misread it?
-- Insert or update the summary row with the new values.
<<insert_update>>
LOOP
UPDATE sales_summary_bytime
SET amount_sold = amount_sold + delta_amount_sold,
units_sold = units_sold + delta_units_sold,
amount_cost = amount_cost + delta_amount_cost
WHERE time_key = delta_time_key;
EXIT insert_update WHEN found;
BEGIN
INSERT INTO sales_summary_bytime (
time_key,
amount_sold,
units_sold,
amount_cost)
VALUES (
delta_time_key,
delta_amount_sold,
delta_units_sold,
delta_amount_cost
);
EXIT insert_update;
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
-- do nothing
END;
END LOOP insert_update;
RETURN NULL;
END;
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB@Harte-Lyne.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
On Wed, Apr 08, 2009 at 04:13:58PM -0400, James B. Byrne wrote:
> Does this mean that the example given on pg. 798 of the manual is in
> error, or have I misread it?
> BEGIN
> EXCEPTION
> WHEN UNIQUE_VIOLATION THEN
> -- do nothing
> END;
please notice that the BEGIN.... EXCEPTION ... END; are withing main
function BEGIN END.
so it has to be like this:
create function x() returns ... as $$
declare
begin
whatever;
begin
do something
exception
end;
whatever;
end;
$$ language plpgsql;
notice 2 pairs of begin/end.
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
On Wed, April 8, 2009 16:06, Tom Lane wrote:
> "James B. Byrne" <byrnejb@harte-lyne.ca> writes:
>> I just need another set of eyes to see whatever it is that I am
>> overlooking.
>
> The RETURN is inside the EXCEPTION clause.
>
> You really need two BEGINs here, one for the outer function body and
> one for the exception block around the INSERT.
>
> regards, tom lane
>
So, something like:
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
BEGIN
INSERT INTO identifiers(...
...
EXCEPTION
WHEN ....
END;
RETURN NULL:
END;
--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB@Harte-Lyne.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
On Wed, 2009-04-08 at 16:06 -0400, Tom Lane wrote: > "James B. Byrne" <byrnejb@harte-lyne.ca> writes: > > I just need another set of eyes to see whatever it is that I am > > overlooking. > > The RETURN is inside the EXCEPTION clause. > > You really need two BEGINs here, one for the outer function body and > one for the exception block around the INSERT. Many people find it surprising that PL/pgSQL allows this occur. It would be good to have a TODO item relating to improved checking of PL/pgSQL functions to avoid runtime errors. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support