Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fA3EJrq92089 for ; Sat, 3 Nov 2001 09:19:53 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id fA3ECU962870 for pgsql-sql@postgresql.org; Sat, 3 Nov 2001 09:12:30 -0500 (EST) (envelope-from news) From: "Edward Grabczewski" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: PL/pgSQL syntax for strings Date: Sat, 3 Nov 2001 14:17:19 -0000 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 77 Message-ID: <9s0u0a$1tcj$1@news.tht.net> References: <20011101125632.A20330@cc.usu.edu> <9rt4jd$u4r$1@news.tht.net> X-Complaints-To: scrappy@hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.00.2919.6600 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 To: pgsql-sql@postgresql.org X-Archive-Number: 200111/35 X-Sequence-Number: 5420 I've sorted this one thanks to John Berkus. Thanks guys. I've included the solution below for your interest. ======================================================= DROP TABLE rtest; DROP INDEX rtest_xz_index; DROP INDEX rtest_yz_index; DROP INDEX rtest_xy_index; CREATE TABLE rtest ( xz BOX, yz BOX, xy BOX); CREATE INDEX rtest_xz_index ON rtest USING RTREE (xz bigbox_ops); CREATE INDEX rtest_yz_index ON rtest USING RTREE (yz bigbox_ops); CREATE INDEX rtest_xy_index ON rtest USING RTREE (xy bigbox_ops); DROP FUNCTION cube (float,float,float, float,float,float); CREATE FUNCTION cube (float,float,float,float,float,float) RETURNS text AS 'DECLARE x1 ALIAS FOR $1; y1 ALIAS FOR $2; z1 ALIAS FOR $3; x2 ALIAS FOR $4; y2 ALIAS FOR $5; z2 ALIAS FOR $6; xz_ BOX; yz_ BOX; xy_ BOX; left VARCHAR; right VARCHAR; BEGIN left := to_char(x1,''99999.999'') || '','' || to_char(z1,''99999.999''); right := to_char(x2,''99999.999'') || '','' || to_char(z2,''99999.999''); xz_ := left || '','' || right; left := to_char(y1,''99999.999'') || '','' || to_char(z1,''99999.999''); right := to_char(y2,''99999.999'') || '','' || to_char(z2,''99999.999''); yz_ := left || '','' || right; left := to_char(x1,''99999.999'') || '','' || to_char(y1,''99999.999''); right := to_char(x2,''99999.999'') || '','' || to_char(y2,''99999.999''); xy_ := left || '','' || right; INSERT INTO rtest(xz,yz,xy) VALUES (xz_, yz_, xy_); RETURN null; END;' LANGUAGE 'plpgsql'; SELECT cube(1,2,3,10,20,30); SELECT * FROM rtest ORDER BY xz USING <<; SELECT xy, yz, xz FROM rtest WHERE xz @ '(0.0,0.0),(2.5,2.5)'::box AND yz @ '(0.0,0.0),(2.5,2.5)'::box AND xy @ '(0.0,0.0),(2.5,2.5)'::box ORDER BY xy USING <<;