Format specifier for integer variables in format() for EXECUTE?
This would 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 the
USING
clause ofEXECUTE
for parameter values. Don't convert them totext
, concatenate and interpret them back. That would be slower and error-prone.Normally you would use the
%I
specifier withformat()
for identifiers like the table name. There is an even better way, though: use aregclass
object-identifier type. Details here:
Table name as a PostgreSQL function parameterUse an
OUT
parameter to simplify your code. Performance is the same.Don't use unquoted CaMeL case identifiers like
getParentLtree
in Postgres. Details in the manual.
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