Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

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

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?
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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:

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...