From: | "Joshua b(dot) Jore" <josh(at)greentechnologist(dot)org> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Nondestructive cluster, equivalent SQL? |
Date: | 2002-05-24 22:43:56 |
Message-ID: | Pine.BSO.4.44.0205241741370.15363-100000@kitten.greentechnologist.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Since the cluster command causes so many hassles with PL/pgSQL functions,
triggers, constraints, indexes etc I'm asking if this spot of SQL emulates
the expected behaviour:
CREATE TABLE SoSIDs (
SoSID CHARACTER(10),
CONSTRAINT SoSIDsPKey PRIMARY KEY (SoSID)
);
CREATE RULE SoSIDsUpd AS ON UPDATE TO SoSIDs
DO INSTEAD NOTHING;
Other PL/pgSQL functions and tables use this table for triggers, foreign
keys and triggers. Running 'CLUSTER SoSIDsPkey ON SoSIDs' is equivalent to
CREATE TABLE cluster_SoSIDs AS SELECT * FROM SoSIDs ORDER BY SoSID;
DROP TABLE SoSIDs;
ALTER TABLE cluster_SoSIDs RENAME TO SoSIDs;
So wouldn't it work just as well and avoid the problems by executing:
CREATE TABLE cluster_SoSIDs AS SELECT * FROM SoSIDs ORDER BY SoSID;
TRUNCATE SoSIDs;
INSERT INTO SoSIDs SELECT * FROM cluster_SoSIDs ORDER BY SoSID;
I'm just looking to hear a yay/nay on whether this is a good idea or not
and if I've got the idea down right. Also, since I'd like to put this
into a PL/pgSQL function how do I get the 'create table ... as ...' part
to work? PL/pgSQL appears to reserve the 'create table ... as select ...'
and 'select * into ... from ...' syntax. I'm not sure how else to
generate the SQL code to do this. Ideas?
Joshua b. Jore ; http://www.greentechnologist.org ; 10012 11010 11022
10202 1012 2122 11020 10202 10202 11002 1020 1012 11102 11102 11102 1201
11001 11002 10211 11020 10202 10202 11002 11021 1201 11010 11020 10211
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-05-24 22:49:49 | Re: Nondestructive cluster, equivalent SQL? |
Previous Message | Tom Ansley | 2002-05-24 18:54:45 | Re: Using CASE with a boolean value |