Why am I getting relation already exists in this execute statement?

Question

Here's the function:

CREATE OR REPLACE FUNCTION get_img(ptype text, pid integer, pdpi integer)
  RETURNS bytea AS
$BODY$
declare psize char(1); pimg bytea;
begin
    select size into psize from dpi_size where dpi in(select max(dpi) from dpi_size where dpi <= pdpi);
    select coalesce(psize, 's') into psize;

    if ptype = 'cat' then
        execute 'select img_' || psize || ' into pimg from cat where id = $1' using pid;
    end if;

    return pimg;

end; $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION get_img(text, integer, integer)
  OWNER TO postgres;

Table cat has img_s, img_m, img_l and an id. Here's what I get when I run select.

select handyman_get_img ('cat', 11, 320)

ERROR: relation "pimg" already exists CONTEXT: SQL statement "select img_l into pimg from cat where id = $1" PL/pgSQL function get_img(text,integer,integer) line 8 at EXECUTE statement

Can anyone enlighten me why it says 'pimg' already exists?

Thanks.

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2014-09-24T08:56:56

You can't use a variable inside the string literal for execute.

The string passed to execute is run "as is" and select .. into pimg from ... is an old (non-standard) syntax that does the same as create table pimg as select ....

If you want to store the result of an execute into a variable you need to use a different syntax:

execute 'select img_' || psize || ' from cat where id = $1' 
        into pimg
        using pid;

Note how the into is outside of the string that is being executed.

Why am I getting relation already exists in this execute statement?

1 Answers