Thread: create temp table in rule

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

Ron Peterson <rpeterso@mtholyoke.edu> writes:
> Is it possible to create a temporary table within a rule?

No, sorry.  From memory, rules only allow SELECT/INSERT/UPDATE/DELETE
plus NOTIFY (and there are restrictions on the latter).
        regards, tom lane