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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

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


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

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;
1
2
3

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

Do you need something else?

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate