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
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 |