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
