Format specifier for integer variables in format () for EXECUTE?

Question

CREATE OR REPLACE FUNCTION getParentLtree(parent_id bigint, tbl_name varchar) 
  RETURNS ltree AS
$BODY$
DECLARE
   parent_ltree ltree;
BEGIN
-- This works fine:
-- select into parent_ltree l_tree from tbl1 where id = parent_id;

EXECUTE format('select into parent_ltree l_tree from %I
                where id = %I', tbl_name,parent_id);

RETURN parent_ltree;
END;
$BODY$ LANGUAGE plpgsql;

There are 2 problems in the function above:

• parent_idis there integer, but is replaced by quotation marks? What is the correct format specifier for variables int?
• select intonot working with EXECUTE? How can I make the above commented request to use the passed table name?

Answer 1

It will be shorter, faster and safer:

CREATE OR REPLACE FUNCTION get_parent_ltree(parent_id bigint, tbl_name regclass
                                          , OUT parent_ltree ltree) AS
$func$
BEGIN

EXECUTE format('SELECT l_tree FROM %s WHERE id = $1', tbl_name)
INTO  parent_ltree
USING parent_id;

END
$func$ LANGUAGE plpgsql;

Why?

• Most importantly, use a parameter for parameter values. Do not convert them to , combine and interpret them. This will be slower and error prone. USING EXECUTEtext

• , , %I specifier format(). : regclass . :
  PostgreSQL

• OUT , . .

• CaMeL, getParentLtree Postgres. .

Answer 2

%s . %I :

select format('select into parent_ltree l_tree from %I  where id = %s', 'tbl1', 1);
                         format                          
---------------------------------------------------------
 select into parent_ltree l_tree from tbl1  where id = 1

http://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-STRING-FORMAT

PL/pgSQL select into Postgresql select into. create table as:

create table parent_ltree as 
select l_tree 
from tbl1
where id = 1

http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

. , SELECT INTO SELECT INTO PostgreSQL, INTO . SELECT PL/pgSQL, CREATE TABLE... AS SELECT.

select into execute:

EXECUTE format('select l_tree from %I where id = %s', tbl_name,parent_id) 
into parent_ltree;

http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN