I had some spare cycles so I went ahead and patched this. Patch includes documentation and new regression tests. While I was in there I also added regression tests for quote_ident(), which appeared to be absent.
quote_literal doesn't seem to have any regression tests either, but I decided to leave that for another patch. With thanks to Neil Conway for his assistance on IRC. Cheers BJ On 9/15/07, Bruce Momjian <[EMAIL PROTECTED]> wrote: > This has been saved for the 8.4 release: > Brendan Jurd wrote: > > Hi hackers, > > > > I note that we currently expose the usefulness of the quote_identifier > > function to the user with quote_ident(text). > > > > Is there any reason we shouldn't do the same with > > quote_qualified_identifier? > > > > We could just add a quote_qualified_ident(text, text) ... it would > > make forming dynamic queries more convenient in databases that use > > multiple schemas. > > > > Clearly a DBA could just create this function himself in SQL (and it > > wouldn't be difficult), but is that a good reason not to have it in > > our standard set of functions? > > > > Would be happy to cook up a patch for this. > > > > Cheers, > > BJ > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 9: In versions below 8.0, the planner will ignore your desire to > > choose an index scan if your joining column's datatypes do not > > match > > -- > Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us > EnterpriseDB http://www.enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + >
Index: doc/src/sgml/func.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.397 diff -c -r1.397 func.sgml *** doc/src/sgml/func.sgml 19 Sep 2007 03:13:57 -0000 1.397 --- doc/src/sgml/func.sgml 22 Sep 2007 03:07:26 -0000 *************** *** 1276,1281 **** --- 1276,1284 ---- <primary>quote_ident</primary> </indexterm> <indexterm> + <primary>quote_qualified_ident</primary> + </indexterm> + <indexterm> <primary>quote_literal</primary> </indexterm> <indexterm> *************** *** 1541,1546 **** --- 1544,1563 ---- </row> <row> + <entry><literal><function>quote_qualified_ident</function>(<parameter>schema</parameter> <type>text</type>, <parameter>identifier</parameter> <type>text</type>)</literal></entry> + <entry><type>text</type></entry> + <entry> + Return the given schema and identifier suitably quoted to be used as a + fully qualified identifier in an <acronym>SQL</acronym> statement + string. Quoting is performed as for <function>quote_ident</function>, + but <parameter>schema</parameter> and <parameter>identifier</parameter> + are quoted separately. + </entry> + <entry><literal>quote_ident('Some schema','A table')</literal></entry> + <entry><literal>"Some schema"."A table"</literal></entry> + </row> + + <row> <entry><literal><function>quote_literal</function>(<parameter>string</parameter>)</literal></entry> <entry><type>text</type></entry> <entry> Index: src/backend/utils/adt/quote.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/quote.c,v retrieving revision 1.22 diff -c -r1.22 quote.c *** src/backend/utils/adt/quote.c 27 Feb 2007 23:48:08 -0000 1.22 --- src/backend/utils/adt/quote.c 22 Sep 2007 03:07:26 -0000 *************** *** 46,51 **** --- 46,77 ---- } /* + * quote_qualified_ident - + * returns a properly quoted, schema-qualified identifier + */ + Datum + quote_qualified_ident(PG_FUNCTION_ARGS) + { + text *schema = PG_GETARG_TEXT_P(0); + text *ident = PG_GETARG_TEXT_P(1); + text *result; + const char *quoted; + char *schema_s; + char *ident_s; + + schema_s = DatumGetCString(DirectFunctionCall1(textout, + PointerGetDatum(schema))); + ident_s = DatumGetCString(DirectFunctionCall1(textout, + PointerGetDatum(ident))); + + quoted = quote_qualified_identifier(schema_s, ident_s); + + result = DatumGetTextP(DirectFunctionCall1(textin, + CStringGetDatum(quoted))); + PG_RETURN_TEXT_P(result); + } + + /* * quote_literal - * returns a properly quoted literal * Index: src/include/catalog/pg_proc.h =================================================================== RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_proc.h,v retrieving revision 1.471 diff -c -r1.471 pg_proc.h *** src/include/catalog/pg_proc.h 20 Sep 2007 17:56:32 -0000 1.471 --- src/include/catalog/pg_proc.h 22 Sep 2007 03:07:34 -0000 *************** *** 2630,2639 **** DATA(insert OID = 1768 ( to_char PGNSP PGUID 12 1 0 f f t f s 2 25 "1186 25" _null_ _null_ _null_ interval_to_char - _null_ _null_ )); DESCR("format interval to text"); ! DATA(insert OID = 1282 ( quote_ident PGNSP PGUID 12 1 0 f f t f i 1 25 "25" _null_ _null_ _null_ quote_ident - _null_ _null_ )); DESCR("quote an identifier for usage in a querystring"); ! DATA(insert OID = 1283 ( quote_literal PGNSP PGUID 12 1 0 f f t f i 1 25 "25" _null_ _null_ _null_ quote_literal - _null_ _null_ )); DESCR("quote a literal for usage in a querystring"); DATA(insert OID = 1798 ( oidin PGNSP PGUID 12 1 0 f f t f i 1 26 "2275" _null_ _null_ _null_ oidin - _null_ _null_ )); DESCR("I/O"); --- 2630,2641 ---- DATA(insert OID = 1768 ( to_char PGNSP PGUID 12 1 0 f f t f s 2 25 "1186 25" _null_ _null_ _null_ interval_to_char - _null_ _null_ )); DESCR("format interval to text"); ! DATA(insert OID = 1282 ( quote_ident PGNSP PGUID 12 1 0 f f t f i 1 25 "25" _null_ _null_ _null_ quote_ident - _null_ _null_ )); DESCR("quote an identifier for usage in a querystring"); ! DATA(insert OID = 1283 ( quote_literal PGNSP PGUID 12 1 0 f f t f i 1 25 "25" _null_ _null_ _null_ quote_literal - _null_ _null_ )); DESCR("quote a literal for usage in a querystring"); + DATA(insert OID = 1285 ( quote_qualified_ident PGNSP PGUID 12 1 0 f f t f i 2 25 "25 25" _null_ _null_ _null_ quote_qualified_ident - _null_ _null_ )); + DESCR("quote a schema-qualified identifier for usage in a querystring"); DATA(insert OID = 1798 ( oidin PGNSP PGUID 12 1 0 f f t f i 1 26 "2275" _null_ _null_ _null_ oidin - _null_ _null_ )); DESCR("I/O"); Index: src/include/utils/builtins.h =================================================================== RCS file: /projects/cvsroot/pgsql/src/include/utils/builtins.h,v retrieving revision 1.303 diff -c -r1.303 builtins.h *** src/include/utils/builtins.h 18 Sep 2007 17:41:17 -0000 1.303 --- src/include/utils/builtins.h 22 Sep 2007 03:07:36 -0000 *************** *** 915,920 **** --- 915,921 ---- /* quote.c */ extern Datum quote_ident(PG_FUNCTION_ARGS); + extern Datum quote_qualified_ident(PG_FUNCTION_ARGS); extern Datum quote_literal(PG_FUNCTION_ARGS); /* guc.c */ Index: src/test/regress/expected/strings.out =================================================================== RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/strings.out,v retrieving revision 1.33 diff -c -r1.33 strings.out *** src/test/regress/expected/strings.out 11 Aug 2007 03:56:24 -0000 1.33 --- src/test/regress/expected/strings.out 22 Sep 2007 03:07:37 -0000 *************** *** 1240,1242 **** --- 1240,1257 ---- a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\ (1 row) + -- + -- Test quoting of identifiers. + -- + select quote_ident('safe') as f1, quote_ident('with space') as f2, quote_ident('punctuation?!') as f3, quote_ident('Cased') as f4, quote_ident('from') as f5, quote_ident('42') as f6, quote_ident('"quoted"') as f7; + f1 | f2 | f3 | f4 | f5 | f6 | f7 + ------+--------------+-----------------+---------+--------+------+-------------- + safe | "with space" | "punctuation?!" | "Cased" | "from" | "42" | """quoted""" + (1 row) + + select quote_qualified_ident('public','safe') as f1, quote_qualified_ident('with space','safe') as f2, quote_qualified_ident('safe','punctuation?!') as f3, quote_qualified_ident('Mixed','cASE') as f4, quote_qualified_ident('from','42') as f5, quote_qualified_ident('"quoted"','"values"') as f6; + f1 | f2 | f3 | f4 | f5 | f6 + -------------+-------------------+----------------------+----------------+-------------+--------------------------- + public.safe | "with space".safe | safe."punctuation?!" | "Mixed"."cASE" | "from"."42" | """quoted"""."""values""" + (1 row) + Index: src/test/regress/sql/strings.sql =================================================================== RCS file: /projects/cvsroot/pgsql/src/test/regress/sql/strings.sql,v retrieving revision 1.22 diff -c -r1.22 strings.sql *** src/test/regress/sql/strings.sql 11 Aug 2007 03:56:24 -0000 1.22 --- src/test/regress/sql/strings.sql 22 Sep 2007 03:07:38 -0000 *************** *** 450,452 **** --- 450,459 ---- set standard_conforming_strings = off; select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd' as f5, '\\\\' as f6; + + -- + -- Test quoting of identifiers. + -- + select quote_ident('safe') as f1, quote_ident('with space') as f2, quote_ident('punctuation?!') as f3, quote_ident('Cased') as f4, quote_ident('from') as f5, quote_ident('42') as f6, quote_ident('"quoted"') as f7; + select quote_qualified_ident('public','safe') as f1, quote_qualified_ident('with space','safe') as f2, quote_qualified_ident('safe','punctuation?!') as f3, quote_qualified_ident('Mixed','cASE') as f4, quote_qualified_ident('from','42') as f5, quote_qualified_ident('"quoted"','"values"') as f6; +
---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend