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

[Ben Morrow]

Quoth dennis :
> 
> I need to copy some data to new table.
> But I encounter some error message.
> the table structure
> Table A:
>   c1  char
>   c2  bytea
> 
> Table B:
>   c1  char
>   c2  bytea
> 
> 
> My sql command:
> insert into B as select * from a where c1=xxx

'AS' isn't valid there. What is xxx? Is it a field you haven't shown us,
or is it a quoted string?

> error:
> operator does not exist: text || bytea

That command (with 'xxx' quoted and the AS removed) doesn't give that
error with those table definitions, so you will need to show us your
actual query.

Ben


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

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

[Ben Morrow]

Quoth dennis :
> 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


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

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

[Ben Morrow]

Quoth dennis :
> 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

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

[Ben Morrow]

Quoth 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

Which Postgres version are you using?

Ben


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

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

[Ben Morrow]

[quoting fixed]

Quoth dennis :
> Ben Morrow wrote:
> > Quoth 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
> >
> > Which Postgres version are you using?
>
> Postgres : 8.1.4

Then I think you want 

create function quote_literal (bytea)
returns text
immutable strict
language plpgsql
as $$ 
begin
return 'E'''
|| replace(encode($1, 'escape'), E'\\', E'')
|| ;
end;
$$;

Ben


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

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

[dennis]

here is example

table name is "mail":
column|   type
-
sender|char
subject   |char
content   |bytea


I want copy some record into new table 'mail_new'.

sql:
create table mail_new as select * from mail sender='dennis'

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'


Ben Morrow 提到:

Quoth dennis :

I need to copy some data to new table.
But I encounter some error message.
the table structure
Table A:
  c1  char
  c2  bytea

Table B:
  c1  char
  c2  bytea


My sql command:
insert into B as select * from a where c1=xxx


'AS' isn't valid there. What is xxx? Is it a field you haven't shown us,
or is it a quoted string?


error:
operator does not exist: text || bytea


That command (with 'xxx' quoted and the AS removed) doesn't give that
error with those table definitions, so you will need to show us your
actual query.

Ben




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

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

[dennis]

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 */ 
 
NOTICE:  select * from usersessiontable order by sessionid limit 1 offset 1
 check_missing_chunk_table_usersessiontable

  1
(1 row)


Ben Morrow 提到:

Quoth dennis :

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




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

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

[dennis]

Postgres : 8.1.4

Dennis


Ben Morrow wrote:

Quoth 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


Which Postgres version are you using?

Ben





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

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 :

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