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 issues in above function:

1. parent_id is integer but it is replaced with quotes? What is the correct format specifier for int variables?
2. select into does not work with EXECUTE? How can I make above commented query to use table name passed?

Answer 1

This would be shorter, faster and safer:

CREATE OR REPLACE FUNCTION get_parent_ltree(parent_id bigint, tbl_name regclass
                                          , OUT parent_ltree ltree)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT l_tree FROM %s WHERE id = $1', tbl_name)
   INTO  parent_ltree
   USING parent_id;
END
$func$;

Why?

Most importantly, use the USING clause of EXECUTE for parameter values. Don't convert them to text, concatenate and interpret them back. That would be slower and error-prone.

Normally you would use the %I specifier with format() for identifiers like the table name. For existing tables, a regclass object-identifier type may be even better. See:

• Table name as a PostgreSQL function parameter

The OUT parameter makes it simpler. Performance is the same.

Don't use unquoted CaMeL case identifiers like getParentLtree in Postgres. Details in the manual.

Answer 2

Use %s for strings. %I is for identifiers:

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's select into is not the same as Postgresql's select into. Use instead 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

Tip: Note that this interpretation of SELECT with INTO is quite different from PostgreSQL's regular SELECT INTO command, wherein the INTO target is a newly created table. If you want to create a table from a SELECT result inside a PL/pgSQL function, use the syntax CREATE TABLE ... AS SELECT.

To select into a variable from an execute statement:

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

Answer 3

Following postgres uses a for loop and variable to creates 10 table starting with prefix 'sbtest' namely sbtest1, sbtest2 ... sbtest10

create_table.sql

do $$
    DECLARE myvar integer;
begin
    for myvar in 1..10 loop
        EXECUTE format('CREATE TABLE sbtest%s (
        id SERIAL NOT NULL,
        k INTEGER NOT NULL,
        c CHAR(120) NOT NULL,
        pad CHAR(60) NOT NULL,
        PRIMARY KEY (id))', myvar);
    end loop;
end; $$

Run it using psql -U user_name -d database_name -f create_table.sql

Example Table  sbtest1 is as
id | k | c | pad
----+---+---+-----
(0 rows)

Table "public.sbtest1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+----------------+-----------+----------+-------------------------------------+----------+------ --------+------------- id | integer | | not null | nextval('sbtest1_id_seq'::regclass) | plain | | k | integer | | not null | | plain | | c | character(120) | | not null | | extended | | pad | character(60) | | not null | | extended | | Indexes: "sbtest1_pkey" PRIMARY KEY, btree (id) Access method: heap