Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

Next:From: Tom LaneDate: 2002-05-24 22:49:49
Subject: Re: Nondestructive cluster, equivalent SQL?
Previous:From: Tom AnsleyDate: 2002-05-24 18:54:45
Subject: Re: Using CASE with a boolean value

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group