Received: from earth.hub.org (earth.hub.org [64.49.215.11]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fA2DDxq08763 for ; Fri, 2 Nov 2001 08:13:59 -0500 (EST) (envelope-from news@news.tht.net) Received: from news.tht.net (news.hub.org [216.126.91.242]) by earth.hub.org (8.11.3/8.11.4) with ESMTP id fA246mL64929 for ; Thu, 1 Nov 2001 23:06:48 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id fA23eVN30879 for pgsql-sql@postgresql.org; Thu, 1 Nov 2001 22:40:31 -0500 (EST) (envelope-from news) From: "Edward Grabczewski" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: PL/pgSQL syntax for strings Date: Fri, 2 Nov 2001 03:45:19 -0000 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 51 Message-ID: <9rt4jd$u4r$1@news.tht.net> References: <20011101125632.A20330@cc.usu.edu> 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/29 X-Sequence-Number: 5414 Sorry about the function not makig sense. It's only half-baked until I get this string thing sorted out. I left the hard-coded numerical values there for testing purposes only - so I guess it confused everyone. As it happens the syntax of the posted function actually works. Even the backslashes! Now the trick is to incorporate the variables into the string. I've looked at the documentation a few times and I've experimented but failed to get the right syntax so far - which is why I thought I'd ask for some help. :-) Below is what the function will eventually look like when it uses the proper variables: I guessed that the single quote needs to be double quoted: ''''('' x1 '','' z1 ''), ('' x2 '','' z2 '')'''' or something like that. Should this work? Eddy 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 ( '(x1,z1), (x2,z2)', '(y1,y2), (y2,z2)', '(x1,y1), (x2,y2)'); RETURN null; END;' LANGUAGE 'plpgsql'; SELECT threedpoint(100,200,300,400,500,600);