Re: Help : insert a bytea data into new table - Mailing list pgsql-sql

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 
problem        execute a_sql;        exception            when others then                raise notice '/* NUM:%,
DETAILS:%*/', SQLSTATE, SQLERRM;                raise notice  'select * from % order by % limit 1 
 
offset %',v_old_table,v_old_order_by,records;        end;        records=records+1;   end loop;   return records;
end;$BODY$  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
ALTER FUNCTION check_missing_chunk_table_usersessiontable() OWNER TO 
postgres;

----------------------run function ---------------------------
select check_missing_chunk_table_usersessiontable();

result:

NOTICE:  /* NUM:42883, DETAILS:operator does not exist: text || bytea */  <<<<<my error message
NOTICE:  select * from usersessiontable order by sessionid limit 1 offset 1 check_missing_chunk_table_usersessiontable
--------------------------------------------                                      1
(1 row)


Ben Morrow 提到:
> Quoth dennis <dennis@teltel.com>:
>> here is example
>>
>> table name is "mail":
>> column    |   type
>> -------------------------
>> sender    |char
>> subject   |char
> 
> I presume you mean 'varchar'?
> 
>> content   |bytea
>>
>>
>> I want copy some record into new table 'mail_new'.
>>
>> sql:
>> create table mail_new as select * from mail sender='dennis'
> 
> You omitted the WHERE. It's very hard to see what's actually going on
> when you keep mis-typing the commands you used.
> 
>> result has an error:
>> operator does not exist: text || bytea
>>
>>
>> But if my sql statement has no column "content"
>> the sql works.
>> sql:
>> create table mail_new as select sender,subject from mail sender='dennis'
> 
> No, it still doesn't give that error for me. Show us something you've
> *actually* *tried*.
> 
> Ben
> 
>