pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to process inverted comma in "EXECUTE 'insert into xxx values(...)'; "?
6+ messages / 3 participants
[nested] [flat]
* How to process inverted comma in "EXECUTE 'insert into xxx values(...)'; "?
@ 2007-05-15 04:51 Nemo Terry <ntuser155@hotmail.com>
2007-05-15 06:25 ` Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...)'; "? Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Nemo Terry @ 2007-05-15 04:51 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...)'; "?
2007-05-15 04:51 How to process inverted comma in "EXECUTE 'insert into xxx values(...)'; "? Nemo Terry <ntuser155@hotmail.com>
@ 2007-05-15 06:25 ` Rodrigo De León <rdeleonp@gmail.com>
2007-05-15 06:50 ` Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...) Nemo Terry <ntuser155@hotmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Rodrigo De León @ 2007-05-15 06:25 UTC (permalink / raw)
To: pgsql-sql; +Cc: Nemo Terry <ntuser155@hotmail.com>
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...)
2007-05-15 04:51 How to process inverted comma in "EXECUTE 'insert into xxx values(...)'; "? Nemo Terry <ntuser155@hotmail.com>
2007-05-15 06:25 ` Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...)'; "? Rodrigo De León <rdeleonp@gmail.com>
@ 2007-05-15 06:50 ` Nemo Terry <ntuser155@hotmail.com>
2007-05-15 07:12 ` Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...) Richard Huxton <dev@archonet.com>
2007-05-15 07:14 ` Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...) Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Nemo Terry @ 2007-05-15 06:50 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...)
2007-05-15 04:51 How to process inverted comma in "EXECUTE 'insert into xxx values(...)'; "? Nemo Terry <ntuser155@hotmail.com>
2007-05-15 06:25 ` Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...)'; "? Rodrigo De León <rdeleonp@gmail.com>
2007-05-15 06:50 ` Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...) Nemo Terry <ntuser155@hotmail.com>
@ 2007-05-15 07:12 ` Richard Huxton <dev@archonet.com>
2007-05-15 07:42 ` Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...) Nemo Terry <ntuser155@hotmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2007-05-15 07:12 UTC (permalink / raw)
To: Nemo Terry <ntuser155@hotmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...)
2007-05-15 04:51 How to process inverted comma in "EXECUTE 'insert into xxx values(...)'; "? Nemo Terry <ntuser155@hotmail.com>
2007-05-15 06:25 ` Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...)'; "? Rodrigo De León <rdeleonp@gmail.com>
2007-05-15 06:50 ` Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...) Nemo Terry <ntuser155@hotmail.com>
2007-05-15 07:12 ` Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...) Richard Huxton <dev@archonet.com>
@ 2007-05-15 07:42 ` Nemo Terry <ntuser155@hotmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Nemo Terry @ 2007-05-15 07:42 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...)
2007-05-15 04:51 How to process inverted comma in "EXECUTE 'insert into xxx values(...)'; "? Nemo Terry <ntuser155@hotmail.com>
2007-05-15 06:25 ` Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...)'; "? Rodrigo De León <rdeleonp@gmail.com>
2007-05-15 06:50 ` Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...) Nemo Terry <ntuser155@hotmail.com>
@ 2007-05-15 07:14 ` Rodrigo De León <rdeleonp@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Rodrigo De León @ 2007-05-15 07:14 UTC (permalink / raw)
To: pgsql-sql; +Cc: Nemo Terry <ntuser155@hotmail.com>
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?
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-05-15 07:42 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-15 04:51 How to process inverted comma in "EXECUTE 'insert into xxx values(...)'; "? Nemo Terry <ntuser155@hotmail.com>
2007-05-15 06:25 ` Rodrigo De León <rdeleonp@gmail.com>
2007-05-15 06:50 ` Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...) Nemo Terry <ntuser155@hotmail.com>
2007-05-15 07:12 ` Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...) Richard Huxton <dev@archonet.com>
2007-05-15 07:42 ` Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...) Nemo Terry <ntuser155@hotmail.com>
2007-05-15 07:14 ` Re: How to process inverted comma in "EXECUTE 'insert into xxx values(...) Rodrigo De León <rdeleonp@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox