trogger problem Opcje

[AcidBurnt]

witam

posiadam triggera wygladajacego tak

[SQL] pobierz, plaintext 
CREATE OR REPLACE FUNCTION cheange_parent(int8, int8)
  RETURNS text AS $BODY$
declare
	row record;
	user ALIAS FOR $1;
	parent ALIAS FOR $2;
BEGIN INSERT 
INTO temida_connections
 (parent_id, child_id, depth) VALUES (parent, user, 1);
	INSERT 
INTO temida_connections
 (parent_id, child_id, depth) 
		SELECT parent_id, user, depth + 1 
FROM temida_connections
 WHERE child_id = parent AND depth > 0;
	FOR row IN SELECT p.child_id, p.parent_id 
FROM temida_users
 k,temida_connections p WHERE parent_id IN (SELECT child_id 
FROM temida_connections
 WHERE parent_id = user) AND k.user_id = p.child_id AND depth=1 ORDER BY parent_id,child_id
LOOP
	INSERT 
INTO temida_connections
 (parent_id, child_id, depth) VALUES (row.child_id, row.parent_id, 1);
	INSERT 
INTO temida_connections
 (parent_id, child_id, depth)
		SELECT parent_id, row.parent_id, depth + 1 
FROM temida_connections
 WHERE child_id = row.child_id AND depth > 0;
END LOOP;
RETURN NULL;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION cheange_parent(int8, int8) OWNER TO postgres;
[SQL] pobierz, plaintext 

moj problem polega na tym ze nie wiem jak zachowac wyniki zapytania

[SQL] pobierz, plaintext 
SELECT p.child_id, p.parent_id 
FROM temida_users
 k,temida_connections p WHERE parent_id IN (SELECT child_id 
FROM temida_connections
 WHERE parent_id = user) AND k.user_id = p.child_id AND depth=1 ORDER BY parent_id,child_id
[SQL] pobierz, plaintext 

tak aby moc je wykorzystac 2 razy w petli for w 1 trigerze

gdyz na poczatku triggera musze wybrac te wiersze i przeoprowadzic petle

[SQL] pobierz, plaintext 
FOR row IN SELECT p.child_id, p.parent_id 
FROM temida_users
 k,temida_connections p WHERE parent_id IN (SELECT child_id 
FROM temida_connections
 WHERE parent_id = user) AND k.user_id = p.child_id AND depth=1 ORDER BY parent_id,child_id
LOOP
DELETE 
FROM temida_connections
 WHERE child_id = row.child_di
END LOOP;
[SQL] pobierz, plaintext 

no i teraz jak pozniej jeszcze raz wykonam zapytanie:

[SQL] pobierz, plaintext 
SELECT p.child_id, p.parent_id 
FROM temida_users
 k,temida_connections p WHERE parent_id IN (SELECT child_id 
FROM temida_connections
 WHERE parent_id = user) AND k.user_id = p.child_id AND depth=1 ORDER BY parent_id,child_id
[SQL] pobierz, plaintext 

nie będzie ono juz posidalo tych wyników ktore poprzednio...


mam nadzieje ze wyrazilem sie w miare jasno

Ten post edytował AcidBurnt 24.01.2006, 01:51:59