-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 18 Dec 2006, at 09:37, Alban Hertroys wrote:
Lars Heidieker wrote:
Hi all,
I just started to write my first stored procedure in plpgsql and
installed a trigger for it.
The two Tables are:
CREATE TABLE ltlocation (
"id" integer DEFAULT nextval('ltlocation_id_seq'::text) NOT NULL,
name varchar(30) NOT NULL default '',
"description" varchar(254) NOT NULL default '',
"parent" int4,
I think you can do without this column; it's already defined by your
location path and it constrains your hierarchy to single parent nodes.
If you're sure single parent nodes are sufficient, you're probably
better off using the ltree contrib package. You'll still have to
handle
tree integrity yourself, but you'll have an optimized index and
functions to navigate the tree.
Yes, that is an option (and I am thinking about it) I only need
single parent nodes, I'll have a look at the ltree package,
the database is just quickly ported from another DBMS and I tried to
get my hands into the stored procedures.
type int2 NOT NULL default '0',
Why the typecast? A string isn't an integer, you know...
True the create script was automatically created and I haven changed
it much.....
(Sure it needs a rewrite)
PRIMARY KEY (id)
) ;
just to hold a tree Structure and the second one is:
CREATE TABLE ltlocationpath (
"ltlocation_id" int4 NOT NULL default '0',
"ltlocancester_id" int4 NOT NULL default '0',
And you're "forcing" your column names to lower case here; whether you
need to is up to you, of course.
That's a left over from the automatic conversion as well.
PRIMARY KEY (ltlocation_id,ltlocancester_id)
) ;
The Stored Procedure is:
CREATE OR REPLACE FUNCTION populatelocationpath() RETURNS trigger
AS $$
DECLARE
workid integer := 0;
BEGIN
IF tg_op = 'UPDATE' THEN
DELETE FROM ltlocationpath WHERE ltlocation_id = old.id;
END IF;
workid := new.id;
Are you sure you want locations to reference themselves? That may also
be where your unique constraint violation originates.
I think I'd use something along the lines of:
workid := new.parent;
LOOP
INSERT INTO ltlocationpath (ltlocation_id, ltlocancester_id)
VALUES (new.id, workid);
SELECT INTO workid ...
-- Assuming the top nodes have NULL parents
EXIT WHEN parent IS NULL;
END LOOP;
That's good to hear that it can be written more easy (no dynamic SQL
necessary in this case)
In your example I don't get the self reference I have to check if I
need to rewrite some other queries for that,
but sure the self reference is actually useless data.
WHILE workid > 0 LOOP
BEGIN
EXECUTE 'INSERT INTO ltlocationpath (ltlocation_id,
ltlocancester_id) '
|| 'VALUES (' || new.id || ', ' || workid || ')';
I don't think you need a dynamic query here (see my example).
EXCEPTION WHEN unique_violation THEN
-- do nothing
END;
SELECT INTO workid parent FROM ltlocation WHERE id = workid;
END LOOP;
RETURN new;
END;
$$ LANGUAGE plpgsql;
And the Trigger is defined as:
CREATE TRIGGER ltlocationpathtrigger AFTER INSERT OR UPDATE ON
ltlocation FOR EACH ROW EXECUTE PROCEDURE populatelocationpath();
The strange thing is:
insert is OK (materialized path gets populated)
update of parent column is OK old values get delete and new ones get
inserted
but if the exception handling of the unique_violation exception is
removed an update on the id column fails, with
an duplicate pkey violation an the self reference in the materialized
path eg for the values (25, 25)
I think that is because your workid will be back at 25 in the next
iteration when that happens, because of the self-reference.
Not sure as I deleted them before, but currently I cant reproduce it.
I just get the following now:
ERROR: insert or update on table "ltlocationpath" violates foreign
key constraint "ltlocancester_fkey"
DETAIL: Key (ltlocancester_id)=(18999) is not present in table
"ltlocation".
CONTEXT: SQL statement "UPDATE ONLY "public"."ltlocationpath" SET
"ltlocation_id" = $1 WHERE "ltlocation_id" = $2"
on: UPDATE ltlocation SET id = 45555 WHERE id = 18999;
which I don't get if:
IF tg_op = 'UPDATE' THEN
DELETE FROM ltlocationpath WHERE ltlocation_id = old.id;
END IF;
is executed.
Probably I am running in some bad interaction between triggers and
foreign key constraints (cascading)
I'll just continue to play around to get a better understanding.
- --
Viele Grüße,
Lars Heidieker
lars@xxxxxxxxxxxx
http://paradoxon.info
- ------------------------------------
Mystische Erklärungen.
Die mystischen Erklärungen gelten für tief;
die Wahrheit ist, dass sie noch nicht einmal oberflächlich sind.
-- Friedrich Nietzsche
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (Darwin)
iD8DBQFFhxxKcxuYqjT7GRYRAgzpAJ9A74MnEFgu7huobM/U6aCK9Y/PlACgxRwW
UHI7RENIUipoarw3UY+Zn9w=
=hJSD
-----END PGP SIGNATURE-----
