From: dennis <dennis@teltel.com>
Subject: Re: Help : insert a bytea data into new table
Date: Thu, 11 Mar 2010 12:09:03 +0800
Organization: Hub.Org Networking Services
Lines: 82
Message-ID: <hn9qd0$152v$1@news.hub.org>
References: <hn49e1$2eob$1@news.hub.org>
	<20100309034626.GA12083@osiris.mauzo.dyndns.org>
	<20100309111410.GA15752@osiris.mauzo.dyndns.org>
	<20100310152510.GA37991@osiris.mauzo.dyndns.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
In-Reply-To: <20100310152510.GA37991@osiris.mauzo.dyndns.org>
To: pgsql-sql@postgresql.org

Dear Ben

    thanks for you anwser.
I try to add function quote_literal on my sql statement .

but it raise other error message (quote_literal not support bytea format):
      function quote_literal(bytea) does not exist


Ben Morrow 提到:
> Quoth dennis <dennis@teltel.com>:
>> Hi Ben
>>
>> here is my function , it's for fix missing chunk problem.
>> It has same problem ,please take look
>>
>>
>> thank for you help
>>
>> -------------table----------------------
>>
>>
>> db=# \d usersessiontable;
>>          Table "public.usersessiontable"
>>    Column   |          Type          | Modifiers
>> -----------+------------------------+-----------
>>   serverid  | character varying(100) |
>>   sessionid | character varying(50)  |
>>   data      | bytea                  |
>> Indexes:
>>      "usersessiontable_idx" btree (sessionid)
>> db=#
>>
>> db=# \d usersessiontable_test;
>>          Table "public.usersessiontable"
>>    Column   |          Type          | Modifiers
>> -----------+------------------------+-----------
>>   serverid  | character varying(100) |
>>   sessionid | character varying(50)  |
>>   data      | bytea                  |
>>
>> ------------------function--------------------------------
>>
>>
>> CREATE OR REPLACE FUNCTION check_missing_chunk_table_usersessiontable()
>>    RETURNS integer AS
>> $BODY$
>> declare
>> begin
>>     records = 0;
>>     OPEN curs1  FOR EXECUTE 'SELECT * FROM usersessiontable ORDER BY 
>> sessionid';
>>     loop
>>          FETCH curs1 INTO rowvar;
>>          IF  NOT FOUND THEN
>>              EXIT;
>>          END IF;
>>          begin
>>          a_sql = 'insert into 
>> usersessiontable_test(sessionid,serverid,data) 
>> values('''||rowvar.sessionid||''','''||rowvar.serverid||''',E'''||rowvar.data||''')';<<--my 
> 
> You are trying to concatenate ''',E''' (of type text) and rowvar.data
> (of type bytea). This is where the error is coming from. (This actually
> works in 8.4, so I presume you're using an earlier version?) In any
> case, this is not a safe way to interpolate into an SQL string: you need
> the quote_literal function.
> 
>     a_sql = 'insert into usersessiontable (sessionid, serverid, data) '
>         || 'values (' || quote_literal(rowvar.sessionid) || ', '
>         || quote_literal(rowvar.serverid) || ', '
>         || quote_literal(rowvar.data) || ')';
> 
> (Is there a function which will do %-interpolation the way RAISE does?
> It would be much clearer in cases like this.)
> 
> Ben
> 
>