Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id C5B469FB5D4 for ; Tue, 15 May 2007 03:50:28 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 54258-09 for ; Tue, 15 May 2007 03:50:20 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from bay0-omc2-s1.bay0.hotmail.com (bay0-omc2-s1.bay0.hotmail.com [65.54.246.137]) by postgresql.org (Postfix) with ESMTP id 1FBD09FB5AB for ; Tue, 15 May 2007 03:50:19 -0300 (ADT) Received: from hotmail.com ([64.4.53.57]) by bay0-omc2-s1.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.2668); Mon, 14 May 2007 23:50:18 -0700 Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Mon, 14 May 2007 23:50:18 -0700 Message-ID: Received: from 67.29.128.55 by by19fd.bay19.hotmail.msn.com with HTTP; Tue, 15 May 2007 06:50:17 GMT X-Originating-IP: [203.125.54.38] X-Originating-Email: [ntuser155@hotmail.com] X-Sender: ntuser155@hotmail.com In-Reply-To: From: "Nemo Terry" To: pgsql-sql@postgresql.org Subject: Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...) Date: Tue, 15 May 2007 14:50:17 +0800 Mime-Version: 1.0 Content-Type: text/plain; charset=gb2312; format=flowed X-OriginalArrivalTime: 15 May 2007 06:50:18.0756 (UTC) FILETIME=[4CF68C40:01C796BD] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.168 tagged_above=0 required=5 tests=BAYES_20=-0.74, DNS_FROM_RFC_ABUSE=0.2, DNS_FROM_RFC_POST=1.708, MSGID_FROM_MTA_HEADER=0 X-Spam-Level: * X-Archive-Number: 200705/118 X-Sequence-Number: 28398 But I must use it in function,so... Do you have another solution? >From: "Rodrigo De Le髇" >To: pgsql-sql@postgresql.org >CC: "Nemo Terry" >Subject: Re: [SQL] How to process inverted comma in "EXECUTE 'insert into xxx values(...)';"? >Date: Tue, 15 May 2007 01:25:25 -0500 > >On 5/14/07, Nemo Terry wrote: >>Look at this problem: >>when >>execute 'insert into lse_installations values(' || >>''''||obj_id||'''' || ',' || ''''||div||'''' || ',' || >>''''||sub||'''' || ',' || ''''||obj_type||'''' || ',' || >>''''||obj_name||'''' || ',' || ''''||pstcd||'''' || ',' || >>''''||rdcd||'''' || ',' || ''''||blkno||'''' || ',' || >>''''||vldunt||'''' || ','|| cenlat || ',' || cenlon || ')'; >>because obj_name from another table has value like this:S'pore High >>Polymer. >>Following error raises: >>ERROR: syntax error at or near "pore" >>SQL state: 42601 >>Context: PL/pgSQL function "lse_installations" line 64 at execute >>statement >> >>So how to process the single inverted comma in char variable?It >>makes me so desperate. > >Why are you EXECUTEing the INSERT command? It's directly supported >in >plpgsql, since it is a superset of SQL. That is, you can do: > >INSERT INTO lse_installations > VALUES (obj_id, div, sub, obj_type, obj_name, pstcd, rdcd, >blkno, vldunt > , cenlat, cenlon); > >Good luck. > >---------------------------(end of >broadcast)--------------------------- >TIP 6: explain analyze is your friend _________________________________________________________________ 与联机的朋友进行交流，请使用 Live Messenger; http://get.live.com/messenger/overview