Using quote_ident () in plpgsql function

Question

I'm just new to creating the plpgsql function. I need some clarification regarding the use of quote_ident () (and even quote_literal ()) for dynamic commands executed inside this function. Hope someone can give me a concrete explanation of how they work inside the function. TIA

Here is an example:

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

Answer 1

quote_identused to quote identifiers. quote_literalused 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)

? , , , ... ? - ( ). , - SQL.

- . quote_literal USING ( ), quote_ident format (- ):

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

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

SQL a) ( ), b) SQL-.