Nondestructive cluster, equivalent SQL?
От | Joshua b. Jore |
---|---|
Тема | Nondestructive cluster, equivalent SQL? |
Дата | |
Msg-id | Pine.BSO.4.44.0205241741370.15363-100000@kitten.greentechnologist.org обсуждение исходный текст |
Ответы |
Re: Nondestructive cluster, equivalent SQL?
|
Список | 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
В списке pgsql-novice по дате отправления: