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

by

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.

More Related Contents:

  1. INSERT with dynamic table name in trigger function
  2. Truncating all tables in a Postgres database
  3. Execute a dynamic crosstab query
  4. Dynamic SQL (EXECUTE) as condition for IF statement
  5. Test for null in function with varying parameters
  6. Return SETOF rows from PostgreSQL function
  7. Error when setting n_distinct using a plpgsql variable
  8. Dynamically generate columns for crosstab in PostgreSQL
  9. Function to loop through and select data from multiple tables
  10. Table name as a PostgreSQL function parameter
  11. Refactor a PL/pgSQL function to return the output of various SELECT queries
  12. The forgotten assignment operator “=” and the commonplace “:=”
  13. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  14. How to set value of composite variable field using dynamic SQL
  15. PostgreSQL parameterized Order By / Limit in table function
  16. How do I do large non-blocking updates in PostgreSQL?
  17. Is there a way to disable function overloading in Postgres
  18. Can I make a plpgsql function return an integer without using a variable?
  19. PostgreSQL function returning multiple result sets
  20. Returning from a function with OUT parameter
  21. DROP FUNCTION without knowing the number/type of parameters?
  22. Use variable set by psql meta-command inside of DO block
  23. COPY with dynamic file name
  24. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  25. ERROR: unterminated quoted string at or near
  26. How can I send some http request from postgresql function or trigger
  27. Select from a table variable
  28. Hashing a String to a Numeric Value in PostgreSQL
  29. plpgsql error “RETURN NEXT cannot have a parameter in function with OUT parameters” in table-returning function
  30. Loop through columns of RECORD

Leave a Comment