Обсуждение: How to process inverted comma in "EXECUTE 'insert into xxx values(...)';"?

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.

_________________________________________________________________
与世界各地的朋友进行交流，免费下载  Live Messenger; http://get.live.com/messenger/overview 

On 5/14/07, Nemo Terry <ntuser155@hotmail.com> 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.

But I must use it in function,so...
Do you have another solution?


>From: "Rodrigo De Le�n" <rdeleonp@gmail.com>
>To: pgsql-sql@postgresql.org
>CC: "Nemo Terry" <ntuser155@hotmail.com>
>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 <ntuser155@hotmail.com> 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 

Nemo Terry wrote:
> But I must use it in function,so...
> Do you have another solution?

>>> because obj_name from another table has value like this:S'pore High 
>>> Polymer.
>>> Following error raises:
>>> ERROR: syntax error at or near "pore"

You'll want to look into the quote_ident() and quote_literal() functions
when constructing queries like this.

See functions and operators / string functions for details.

--  Richard Huxton Archonet Ltd

On 5/15/07, Nemo Terry <ntuser155@hotmail.com> wrote:
> But I must use it in function,so...
> Do you have another solution?

So?

----------------------------------------

t=# CREATE TABLE d(i INT);
CREATE TABLE
t=# CREATE OR REPLACE FUNCTION add1(x INT) RETURNS INT AS
t-# $$
t$# BEGIN
t$#   INSERT INTO d VALUES(x);
t$#   RETURN x;
t$# END;
t$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
t=# SELECT add1(1);   1

t=# SELECT add1(2);   2

t=# SELECT add1(3);   3

t=# SELECT * FROM d;123

----------------------------------------

Do you need something else?

quote_literal() works.Thanks a lot!


>From: Richard Huxton <dev@archonet.com>
>To: Nemo Terry <ntuser155@hotmail.com>
>CC: pgsql-sql@postgresql.org
>Subject: Re: [SQL] How to process inverted comma in "EXECUTE 'insert into xxx values(...)
>Date: Tue, 15 May 2007 08:12:55 +0100
>
>Nemo Terry wrote:
> > But I must use it in function,so...
> > Do you have another solution?
>
> >>> because obj_name from another table has value like this:S'pore High
> >>> Polymer.
> >>> Following error raises:
> >>> ERROR: syntax error at or near "pore"
>
>You'll want to look into the quote_ident() and quote_literal() functions
>when constructing queries like this.
>
>See functions and operators / string functions for details.
>
>--
>   Richard Huxton
>   Archonet Ltd
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend

_________________________________________________________________
享用世界上最大的电子邮件系统― MSN Hotmail。 http://www.hotmail.com