From: | Ben Morrow <ben(at)morrow(dot)me(dot)uk> |
---|---|
To: | dennis(at)teltel(dot)com, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Help : insert a bytea data into new table |
Date: | 2010-03-10 15:25:10 |
Message-ID: | 20100310152510.GA37991@osiris.mauzo.dyndns.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Quoth dennis <dennis(at)teltel(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | dennis | 2010-03-11 04:09:03 | Re: Help : insert a bytea data into new table |
Previous Message | Bryce Nesbitt | 2010-03-10 07:26:13 | Remote monitoring of Postgres w/minimal grants |