Re: Help : insert a bytea data into new table

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