Creating Tables in Triggers/Functions

[JWHardcastle]

I'm trying to use a pair of triggers and functions to generate a hierarchically sorted list of Locations based on a linear table. Basically, each location has a parent from within the locations table. I want to create an alphabetical list where children are listed after their parents. I've done this successfully on SQL server, but can't get it working on Postgres.

I have a trigger on the Locations table for AFTER UPDATE INSERT DELETE that generates the tree from scratch. The trigger just calls parsetree().

BEGIN
	CREATE TABLE "stack" (
	  locid INTEGER,
	  lvl SMALLINT
	) WITHOUT OIDS;
	
	DELETE FROM "lochier";

	CREATE TRIGGER "parse_tree_help" BEFORE INSERT OR UPDATE OR DELETE
	ON stack FOR EACH ROW
	EXECUTE PROCEDURE "parsetreehelp"();

	INSERT INTO "stack"(locid,lvl)
		SELECT LocID, 1
		FROM Locations
		WHERE ParentID IS NULL
		ORDER BY LocName;

    DROP TABLE "stack";

	RETURN NULL;
END;

Parsetree then calls parestreehelp():

BEGIN
	INSERT INTO lochier(locid, lvl)
	VALUES(NEW.locid, NEW.lvl);

	INSERT INTO "stack"
		SELECT LocID, NEW.lvl+1
		FROM Locations
		WHERE ParentID = NEW.locid
		ORDER BY LocName;
		
	RETURN NEW;
END;

If I create these functions in the right order, they work once, then immediately afteward claim that "relation 17xxx does not exist" where 17xxx is a number that is slowly going up everytime I try this.

Any ideas? I thought it might have something to do with stack, but quoting it didn't seem to help. Thanks in advance.

 

[Leland123]

Hi JWHardcastle,

You might take a look at the postgres cookbook which has a number example functions and triggers. Maybe you will find something there that will give you an idea how to write your trigger.

http://www.brasileiro.net:8080/postgres/cookbook/

LelandJ Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox

 

[JWHardcastle]

That's exactly what I was looking for and couldn't fine. Thanks!