Define table and column names as arguments in a plpgsql function?

by

You must defend against SQL injection whenever you turn user input into code. That includes table and column names coming from system catalogs or from direct user input alike. This way you also prevent trivial exceptions with non-standard identifiers. There are basically three built-in methods:

1. format()

1st query, sanitized:

CREATE OR REPLACE FUNCTION foo(_t text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('
   ALTER TABLE %I ADD COLUMN c1 varchar(20)
                , ADD COLUMN c2 varchar(20)', _t);
END
$func$;

format() requires Postgres 9.1 or later. Use it with the %I format specifier.

The table name alone may be ambiguous. You may have to provide the schema name to avoid changing the wrong table by accident. Related:

Aside: adding multiple columns with a single ALTER TABLE command is cheaper.

2. regclass

You can also use a cast to a registered class (regclass) for the special case of existing table names. Optionally schema-qualified. This fails immediately and gracefully for table names that are not be valid and visible to the calling user. The 1st query sanitized with a cast to regclass:

CREATE OR REPLACE FUNCTION foo(_t regclass)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE 'ALTER TABLE ' || _t || ' ADD COLUMN c1 varchar(20)
                                   , ADD COLUMN c2 varchar(20)';
END
$func$;

Call:

SELECT foo('table_name');

Or:

SELECT foo('my_schema.table_name'::regclass);

Aside: consider using just text instead of varchar(20).

3. quote_ident()

The 2nd query sanitized:

CREATE OR REPLACE FUNCTION foo(_t regclass, _c text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE 'UPDATE ' || _t    -- sanitized with regclass
        || ' SET ' || quote_ident(_c) || ' = ''This is a test''';
END
$func$;

For multiple concatenations / interpolations, format() is cleaner …

Related answers:

Case sensitive!

Be aware that unquoted identifiers are not cast to lower case here. When used as identifier in SQL [Postgres casts to lower case automatically][7]. But here we pass strings for dynamic SQL. When escaped as demonstrated, CaMel-case identifiers (like UserS) will be preserved by doublequoting ("UserS"), just like other non-standard names like "name with space" "SELECT"etc. Hence, names are case sensitive in this context.

My standing advice is to use legal lower case identifiers exclusively and never worry about that.

Aside: single quotes are for values, double quotes are for identifiers. See:

More Related Contents:

  1. Table name as a PostgreSQL function parameter
  2. Can I make a plpgsql function return an integer without using a variable?
  3. No function matches the given name and argument types
  4. Refactor a PL/pgSQL function to return the output of various SELECT queries
  5. INSERT with dynamic table name in trigger function
  6. Difference between language sql and language plpgsql in PostgreSQL functions
  7. Truncating all tables in a Postgres database
  8. PostgreSQL parameterized Order By / Limit in table function
  9. Committing transactions while executing a postgreql Function
  10. Execute a dynamic crosstab query
  11. Static vs class functions/variables in Swift classes?
  12. Dynamic SQL (EXECUTE) as condition for IF statement
  13. User defined variables in PostgreSQL
  14. DROP FUNCTION without knowing the number/type of parameters?
  15. Test for null in function with varying parameters
  16. Return SETOF rows from PostgreSQL function
  17. Error when setting n_distinct using a plpgsql variable
  18. Dynamically generate columns for crosstab in PostgreSQL
  19. Function to loop through and select data from multiple tables
  20. COPY with dynamic file name
  21. Loop on tables with PL/pgSQL in Postgres 9.0+
  22. Format specifier for integer variables in format() for EXECUTE?
  23. In R, what exactly is the problem with having variables with the same name as base R functions?
  24. Understanding how recursive functions work
  25. How to get the original variable name of variable passed to a function
  26. Value receiver vs. pointer receiver
  27. Use a variable to define a PHP function
  28. Return value from a VBScript function
  29. Undefined variable problem with PHP function
  30. Cursor based records in PostgreSQL

Leave a Comment