Date: Wed, 10 Mar 2010 15:25:10 +0000
From: Ben Morrow <ben@morrow.me.uk>
To: dennis@teltel.com, pgsql-sql@postgresql.org
Subject: Re: Help : insert a bytea data into new table
Message-ID: <20100310152510.GA37991@osiris.mauzo.dyndns.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=us-ascii
Content-Disposition: inline
In-Reply-To: <hn710t$16bp$1@news.hub.org>
Organization: Who, me?
User-Agent: Mutt/1.5.20 (2009-06-14)

Quoth dennis <dennis@teltel.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