From: Ron Peterson <rpeterso@mtholyoke.edu>
To: pgsql-sql@postgresql.org
Subject: create temp table in rule
Date: Tue, 18 Apr 2006 14:42:10 -0400
Message-ID: <20060418184209.GB31915@mtholyoke.edu> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rpeterso@mtholyoke.edu
Subject: Re: create temp table in rule
In-Reply-To: <20060418184209.GB31915@mtholyoke.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox