Contents
quote_ident()
A function which formats identifiers for use in SQL strings
quote_ident() is a system function which formats an identifier for use in an SQL statement string.
quote_ident() was added in PostgreSQL 7.1.
Usage
quote_ident ( text ) → text
quote_ident() is commonly used in PL/pgSQL, along with quote_literal(), to generate dynamic queries using variables.
Change history
- PostgreSQL 7.1
- added (commit daf1e3a7)
Examples
Basic usage:
postgres=# SELECT quote_ident('Foo "Bar"');
quote_ident
---------------
"Foo ""Bar"""
(1 row)
Practical use case:
postgres=# CREATE TABLE "FOO" (id SERIAL); CREATE TABLE postgres=# SELECT pg_get_serial_sequence(quote_ident('FOO'), 'id'); pg_get_serial_sequence ------------------------ public."FOO_id_seq" (1 row)
Usage in PL/pgSQL:
postgres=# CREATE OR REPLACE FUNCTION dyntest (
tablename TEXT,
colname TEXT
)
RETURNS INT
LANGUAGE plpgsql STABLE
AS $$
DECLARE
intval INT;
BEGIN
EXECUTE 'SELECT ' || quote_ident(colname) ||
'FROM ' || quote_ident(tablename) ||
'LIMIT 1'
INTO intval;
RETURN intval;
END;
$$;
postgres=# INSERT INTO "FOO" values (1);
INSERT 0 1
postgres=# SELECT dyntest('FOO', 'ID');
dyntest
---------
1
(1 row)
References
- PostgreSQL documentation: Other String Functions