From: dennis <dennis@teltel.com>
Subject: Re: Help : insert a bytea data into new table
Date: Wed, 10 Mar 2010 10:43:47 +0800
Organization: Hub.Org Networking Services
Lines: 118
Message-ID: <hn710t$16bp$1@news.hub.org>
References: <hn49e1$2eob$1@news.hub.org>
	<20100309034626.GA12083@osiris.mauzo.dyndns.org>
	<20100309111410.GA15752@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: <20100309111410.GA15752@osiris.mauzo.dyndns.org>
To: pgsql-sql@postgresql.org

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
> 
>