Thread: PLPGSQL - Trigger Insert acting strange

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

Hadley Willan <hadley.willan@deeper.co.nz> writes:
> 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 don't believe you want quote_literal() here.  plpgsql can pass
variables into queries just fine...

            regards, tom lane