Is it possible to create a temporary table within a rule?
I get a syntax error near 'CREATE TEMPORARY TABLE' if I try to do the
following:
CREATE RULE id_insert_rule AS
ON INSERT TO id_insert
DO INSTEAD
( CREATE TEMPORARY TABLE id_temp ( LIKE id ) ON COMMIT DELETE ROWS;
INSERT INTO id_temp ( username, pass_md5, pass_sha1 ) VALUES new.username, encode( digest( new.password,
'md5'), 'hex' ), encode( digest( new.password, 'sha1' ), 'hex' );
DELETE FROM id USING id_temp WHERE id.username = id_temp.username;
INSERT INTO id SELECT * FROM id_temp;
);
As you can see, I'm trying to create a simple 'insert or update' rule.
--
Ron Peterson
Network & Systems Manager
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso