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

Question

It must be simple, but I'm making my first steps into Postgres functions and I can't find anything that works...

I'd like to create a function that will modify a table and / or column and I can't find the right way of specifying my tables and columns as arguments in my function.

Something like:

CREATE OR REPLACE FUNCTION foo(t table)
RETURNS void AS $$
BEGIN
   alter table t add column c1 varchar(20);
   alter table t add column c2 varchar(20);
   alter table t add column c3 varchar(20);
   alter table t add column c4 varchar(20);
END;
$$ LANGUAGE PLPGSQL;

select foo(some_table)

In another case, I'd like to have a function that alters a certain column from a certain table:

CREATE OR REPLACE FUNCTION foo(t table, c column)
RETURNS void AS $$
BEGIN
   UPDATE t SET c = "This is a test";
END;
$$ LANGUAGE PLPGSQL;

Is it possible to do that?

Croix

Have a look at this post. – Anticatalyst

Answer 1

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:

• INSERT with dynamic table name in trigger function
• How does the search_path influence identifier resolution and the "current schema"

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:

• Table name as a PostgreSQL function parameter
• Postgres functions vs prepared queries

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:

• Are PostgreSQL column names case-sensitive?

Mims