Thread: 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
Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...)';"?
From
"Rodrigo De León"
Date:
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
Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...)
From
Richard Huxton
Date:
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
Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...)
From
"Rodrigo De León"
Date:
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