From: dennis <dennis@teltel.com>
To: pgsql-sql@postgresql.org
Subject: Re: Help : insert a bytea data into new table
Date: Wed, 10 Mar 2010 10:43:47 +0800
Message-ID: <hn710t$16bp$1@news.hub.org> (raw)
In-Reply-To: <20100309111410.GA15752@osiris.mauzo.dyndns.org>
References: <hn49e1$2eob$1@news.hub.org>
<20100309034626.GA12083@osiris.mauzo.dyndns.org>
<20100309111410.GA15752@osiris.mauzo.dyndns.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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dennis@teltel.com
Subject: Re: Help : insert a bytea data into new table
In-Reply-To: <hn710t$16bp$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox