Re: [SQL] Help : insert a bytea data into new table

[dennis]

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 <den...@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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql