Re: Help : insert a bytea data into new table

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(at)teltel(dot)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
>
>