pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcreate temp table in rule
2+ messages / 2 participants
[nested] [flat]
* create temp table in rule
@ 2006-04-18 18:42 Ron Peterson <rpeterso@mtholyoke.edu>
0 siblings, 1 reply; 2+ messages in thread
From: Ron Peterson @ 2006-04-18 18:42 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: create temp table in rule
@ 2006-04-18 19:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ron Peterson <rpeterso@mtholyoke.edu>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2006-04-18 19:53 UTC (permalink / raw)
To: Ron Peterson <rpeterso@mtholyoke.edu>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-04-18 19:53 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-18 18:42 create temp table in rule Ron Peterson <rpeterso@mtholyoke.edu>
2006-04-18 19:53 ` Tom Lane <tgl@sss.pgh.pa.us>
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