the use of quote_ident() in a plpgsql function

Question

I am just new in creating plpgsql function.I need some clarifications regarding the use of quote_ident() (and even quote_literal()) on dynamic commands being executed inside the function. Hope that anybody could give me a specific explanation on how they worked inside a function. TIA

Here's an example of it:

EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_literal(newvalue) || ' WHERE key = ' || quote_literal(keyvalue);

Solution

quote_ident is used for identifiers quoting. quote_literal is used for string quoting.

postgres=# select quote_ident('tablename');
┌─────────────┐
│ quote_ident │
╞═════════════╡
│ tablename   │
└─────────────┘
(1 row)

postgres=# select quote_ident('special name');
┌────────────────┐
│  quote_ident   │
╞════════════════╡
│ "special name" │
└────────────────┘
(1 row)

postgres=# select quote_literal(e'some text with special char"\'"');
┌───────────────────────────────────┐
│           quote_literal           │
╞═══════════════════════════════════╡
│ 'some text with special char"''"' │
└───────────────────────────────────┘
(1 row)

What is identifier? Names of tables, columns, schemas, sequences, ... What is literal? - usually some text value (but can be a value of any type). Both function search and replace some special chars, but with different rules - identifiers and strings are different in SQL.

Now - these functions are little bit obsolete. quote_literal should be replaced by clause USING (better performance), quote_ident should be replaced by formatting function format (due better readability):

EXECUTE format('UPDATE tbl SET %I=$1 WHERE key=$2', colname) 
  USING newvalue, keyvalue;

or only with format function

EXECUTE format('UPDATE tbls SET %I=%L WHERE key=%L', colname, newvalue, keyvalue);

Without quoting your dynamic SQL a) should not work (fails on syntax error), b) be unsafe against sql injection.

Comments

[raphael]

reference: postgresql.org/docs/current/static/…. Also format should be used because: "This form is better because the variables are handled in their native data type format, rather than unconditionally converting them to text and quoting them via %L. It is also more efficient"

[Pavel Stehule]

@raphael - using format with placeholder %L is exactly same like quote_literal. The more effective method is using USING clause.