Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fA1H6xP63352 for ; Thu, 1 Nov 2001 12:06:59 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id fA1Ghu008724 for pgsql-sql@postgresql.org; Thu, 1 Nov 2001 11:43:56 -0500 (EST) (envelope-from news) From: "Edward Grabczewski" X-Newsgroups: comp.databases.postgresql.sql Subject: PL/pgSQL syntax for strings Date: Thu, 1 Nov 2001 16:48:29 -0000 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 78 Message-ID: <9rru48$8gh$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/17 X-Sequence-Number: 5403 Could someone please tell me the correct syntax for the following problem in plpgsql: The following expression code sample works but now (for example) I'd like to subsitute the variables $1 and $2 for the numbers 0 and 2 in the following expression: INSERT INTO rtest(xz,yz,xy) VALUES ( \'(0,2), (1,3)\', \'(1,2), (2,3)\', \'(0,1), (1,2)\'); What's the correct way of represinting the strings? A complete code example is below. It's to do with the usage of R-tree indexing. Eddy edward.grabczewski@btinternet.com =============================================================== 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 threedpoint (float,float,float, float,float,float); CREATE FUNCTION threedpoint (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; BEGIN INSERT INTO rtest(xz,yz,xy) VALUES ( \'(0,2), (1,3)\', \'(1,2), (2,3)\', \'(0,1), (1,2)\'); RETURN null; END;' LANGUAGE 'plpgsql'; SELECT threedpoint(100,200,300,400,500,600); SELECT * FROM rtest ORDER BY xz USING <<; SELECT xz, yz, xy FROM rtest WHERE xz @ '(1.0,3.0),(0.0,2.0)'::box AND yz @ '(2.0,3.0),(1.0,2.0)'::box AND xy @ '(1.0,2.0),(0.0,1.0)'::box ORDER BY xz USING <<; ======================================================== -- Eddy Grabczewski edward.grabczewski@btinternet.com