No return from trigger function

Поиск
Список
Период
Сортировка
От James B. Byrne
Тема No return from trigger function
Дата
Msg-id 53451.216.185.71.24.1239220762.squirrel@webmail.harte-lyne.ca
обсуждение исходный текст
Ответы Re: No return from trigger function  (hubert depesz lubaczewski <depesz@depesz.com>)
Re: No return from trigger function  (Guillaume Lelarge <guillaume@lelarge.info>)
Re: No return from trigger function  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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@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


В списке pgsql-general по дате отправления:

Предыдущее
От: Ian Mayo
Дата:
Сообщение: Re: Are there performance advantages in storing bulky field in separate table?
Следующее
От: hubert depesz lubaczewski
Дата:
Сообщение: Re: No return from trigger function