PLPGSQL - Trigger Insert acting strange

Поиск
Список
Период
Сортировка
От Hadley Willan
Тема PLPGSQL - Trigger Insert acting strange
Дата
Msg-id 1035174727.10158.45.camel@orac.area51.deeper.co.nz
обсуждение исходный текст
Ответы Re: PLPGSQL - Trigger Insert acting strange  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
Hello,
    Maybe I've missed something really obvious but I'm getting some odd
behaviour when I try and do a delete without using a query-string.

Trigger is AFTER DELETE.

Calls a function which in turn calls another function etc...

However, my problem is that Example One is the simpler of the two, but
doesn't seem to work... it executes but does not return an error OR
delete the target row...

==================================================================
EXAMPLE ONE
==================================================================
CREATE OR REPLACE FUNCTION fn_del_pol_grp_folder_unit(
          VARCHAR(32),
          INTEGER,
          INTEGER ) RETURNS BOOLEAN AS'
DECLARE
        _policy_group ALIAS FOR $1;
        _folder ALIAS FOR $2;
        _unit ALIAS FOR $3;
BEGIN
        RAISE NOTICE ''fn_del_pol_grp_folder_unit, %,%,%'',
        _policy_group, _folder, _unit;

        DELETE FROM policy_group_folder_unit
         WHERE policy_group = quote_literal(_policy_group)
         AND folder = _folder
         AND unit_number = _unit;

        RETURN TRUE;
END;'
language 'plpgsql';
====================================================================

I've coded around it by using a query string which executes fine,
deletes the row etc.. (example two).

====================================================================
EXAMPLE TWO
====================================================================
CREATE OR REPLACE FUNCTION fn_del_pol_grp_folder_unit(
         VARCHAR(32),
         INTEGER,
         INTEGER ) RETURNS BOOLEAN AS'
DECLARE
        _policy_group ALIAS FOR $1;
        _folder ALIAS FOR $2;
        _unit ALIAS FOR $3;

        policy_group VARCHAR := ''policy_group'';
        folder VARCHAR := ''folder'';
        unit_number VARCHAR := ''unit_number'';
BEGIN
        RAISE NOTICE ''fn_del_pol_grp_folder_unit, %,%,%'',
        _policy_group, _folder, _unit;

        EXECUTE ''DELETE FROM policy_group_folder_unit ''   ||
                '' WHERE '' || quote_ident(policy_group)    || ''=''
                            || quote_literal(_policy_group) ||
                '' AND ''   || quote_ident(folder)          || ''=''
                            || quote_literal(_folder)       ||
                '' AND ''   || quote_ident(unit_number)     || ''=''
                            || quote_literal(_unit);

        RETURN TRUE;

END;'
language 'plpgsql';
======================================================================

However, as I said earlier, I'm at a loss as to why example one does not
work? or raise an error?

If anyone can offer a reason or point out my "numb-skull-ed-ness" that
would be great.

Thanks,
    Hadley


--
Hadley Willan > Systems Development > Deeper Design Limited.
hadley@deeper.co.nz > www.deeperdesign.com > +64 (21) 28 41 463



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

Предыдущее
От: Jean-Christian Imbeault
Дата:
Сообщение: Listmaster: help please
Следующее
От: Tom Lane
Дата:
Сообщение: Re: PLPGSQL - Trigger Insert acting strange