No return from trigger function

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

"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

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

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