From: Ron Peterson <rpeterso@mtholyoke.edu>
Date: Tue, 18 Apr 2006 14:42:10 -0400
To: pgsql-sql@postgresql.org
Subject: create temp table in rule
Message-ID: <20060418184209.GB31915@mtholyoke.edu>
Mail-Followup-To: pgsql-sql@postgresql.org
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Organization: Mount Holyoke College
User-Agent: Mutt/1.5.9i

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