PLPGSQL - Trigger Insert acting strange

From: Hadley Willan <hadley(dot)willan(at)deeper(dot)co(dot)nz>
To: pgsql-general(at)postgresql(dot)org
Subject: PLPGSQL - Trigger Insert acting strange
Date: 2002-10-21 04:32:07
Message-ID: 1035174727.10158.45.camel@orac.area51.deeper.co.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)deeper(dot)co(dot)nz > www.deeperdesign.com > +64 (21) 28 41 463

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2002-10-21 04:46:45 Re: PLPGSQL - Trigger Insert acting strange
Previous Message Jean-Christian Imbeault 2002-10-21 04:15:44 Listmaster: help please