m4 macros plus PostgreSQL anyone?

Has anyone used the macro processor m4 to generate PostgreSQL (or any
other sql) code automatically from a data dictionary? We have made a
good start on this, but would like to avoid reinventing any wheels, or
wasting time down blind alleys.

To illustrate the benefits of this approach, consider the following
example:

UserTable(
User,
{
{fId, text,},
{fName, text,},
{pRole, integer,},
{pGroup, integer,},
{fOk, integer,1}},
{
{fId}})

This is automatically expanded via our macro library into the
maintenance script listed at the bottom of this message. Please excuse
the obvious mistakes. We are still debugging and have only been using
PostgreSQL a couple of months and m4 for a couple of days. Any advice or
recount of your own experiences would be appreciated.

About Us: we are an IT startup based in Tasmania. It is our mission to
reengineer corporate database systems for the web using strictly open
source technology and state of the art methodologies. Naturally the bulk
of what we develop will eventually be released as open source as well,
once we get our business model sorted out. (My partner and I bet our
houses and our careers on PostgreSQL and this little venture. Any
advice on that would be welcome too. <grin>)

Cheers,
Andrew Smith

---

/* rebuild user table tUsers */

ALTER TABLE tUsers RENAME TO tOldUsers;
DROP SEQUENCE nUser;
CREATE SEQUENCE nUser;

CREATE TABLE tUsers
(
kUser INTEGER NOT NULL DEFAULT NEXTVAL('nUser'),
fId TEXT NOT NULL,
fName TEXT NOT NULL,
pRole INTEGER NOT NULL,
pGroup INTEGER NOT NULL,
fOk INTEGER NOT NULL DEFAULT 1
);

GRANT ALL ON tUsers TO PUBLIC;
BEGIN;
INSERT INTO tUsers (kUser,fId,fName,pRole,pGroup,fOk)
SELECT kUser,fId,fName,pRole,pGroup,fOk FROM tOldUsers;
SELECT SETVAL('nUser',(SELECT MAX(kUser) FROM tUsers)) FROM tUsers LIMIT
1;
COMMIT;
DROP TABLE tOldUsers;
CREATE INDEX tUsers0 ON tUsers(kUser);
CREATE INDEX tUsers1 ON tUsers(fId);

/* rebuild user history table hUsers */

ALTER TABLE hUsers RENAME TO hOldUsers;
DROP SEQUENCE gUser;
CREATE SEQUENCE gUser;

CREATE TABLE hUsers
(
jUser INTEGER NOT NULL DEFAULT NEXTVAL('gUser'),
hUser INTEGER NOT NULL,
hAction CHAR NOT NULL,
hWhen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
kUser INTEGER NOT NULL,
fId TEXT NOT NULL,
fName TEXT NOT NULL,
pRole INTEGER NOT NULL,
pGroup INTEGER NOT NULL,
fOk INTEGER NOT NULL DEFAULT 1
);

GRANT ALL ON hUsers TO PUBLIC;
BEGIN;
INSERT INTO hUsers
(jUser,hUser,hAction,hWhen,kUser,fId,fName,pRole,pGroup,fOk)
SELECT jUser,hUser,hAction,hWhen,kUser,fId,fName,pRole,pGroup,fOk
FROM hOldUsers;
SELECT SETVAL('gUser',(SELECT MAX(jUser) FROM hUsers)) FROM hUsers LIMIT
1;
COMMIT;
DROP TABLE hOldUsers;
CREATE INDEX hUsers0 ON hUsers(jUser);
CREATE INDEX hUsers1 ON hUsers(kUser);

SELECT SETVAL('nUser',(SELECT MAX(kUser) FROM hUsers)) FROM hUsers LIMIT
1;

/* create rules for history table maintenance */

VACUUM ANALYZE pg_proc; DROP RULE rUsersU0;
VACUUM ANALYZE pg_proc; DROP RULE rUsersD0;
VACUUM ANALYZE pg_proc; DROP RULE rUsersI0;
VACUUM ANALYZE pg_proc; DROP VIEW vUsers;
VACUUM ANALYZE pg_proc;
CREATE VIEW vUsers AS SELECT a.fId AS
hUser,b.kUser,b.fId,b.fName,b.pRole,b.pGroup,b.fOk FROM tUsers a,tUsers
b;
GRANT ALL ON vUsers TO PUBLIC;
VACUUM ANALYZE pg_proc;
CREATE RULE rUsersI0 AS ON INSERT TO vUsers DO INSTEAD
(
INSERT INTO tUsers(fId,fName,pRole,pGroup,fOk) VALUES
(new.fId,new.fName,new.pRole,new.pGroup,new.fOk);
INSERT INTO
hUsers(hUser,hAction,hWhen,kUser,fId,fName,pRole,pGroup,fOk)
SELECT a.kUser,'i',CURRENT_TIMESTAMP,(SELECT last_value FROM
nUser),new.fId,new.fName,new.pRole,new.pGroup,new.fOk
FROM tUsers a WHERE a.fId = new.hUser;
);
CREATE RULE rUsersD0 AS ON DELETE TO vUsers DO INSTEAD
(
INSERT INTO
hUsers(hUser,hAction,hWhen,kUser,fId,fName,pRole,pGroup,fOk)
SELECT
a.kUser,'d',CURRENT_TIMESTAMP,old.kUser,old.fId,old.fName,old.pRole,old.pGroup,old.fOk

FROM tUsers a WHERE a.fId = old.hUser;
DELETE FROM tUsers WHERE kUser = old.kUser;
);
CREATE RULE rUsersU0 AS ON UPDATE TO vUsers DO INSTEAD
(
INSERT INTO
hUsers(hUser,hAction,hWhen,kUser,fId,fName,pRole,pGroup,fOk)
SELECT
a.kUser,'u',CURRENT_TIMESTAMP,new.kUser,new.fId,new.fName,new.pRole,new.pGroup,new.fOk

FROM tUsers a WHERE a.fId = new.hUser;
UPDATE tUsers SET
fId = new.fId,
fName = new.fName,
pRole = new.pRole,
pGroup = new.pGroup,
fOk = new.fOk
WHERE kUser = old.kUser;
);