Nondestructive cluster, equivalent SQL?

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

Responses

Browse pgsql-novice by date

  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