Adriaan van Os wrote:
> Tom Lane wrote:
> > Bruce Momjian <bruce@momjian.us> writes:
> >>> I presume the reason for that is that the first paramater can be
> >>> qualified:
> >>> select pg_get_serial_sequence('"public"."FOO"', 'Ff1');
> >
> >> Would someone explain why qualification makes us lowercase the first
> >> parameter by default? I don't understand it well enough to document it.
> >
> > The point is that we have to parse the first parameter, whereas the
> > second one can be taken literally.
>
> It still looks inconsistent and ugly. I think the design mistake of pg_get_serial_sequence is that
> it takes two parameters rather than one (a fully qualified doublequoted columnname path) or three
> (optionally empty schema, tablename, columnname, all three literal).
I did my best to document the behavior of pg_get_serial_sequence().
There actually is a technical reason why we can't auto-quote the first
parameter. Patch applied to HEAD and 8.2.X.
Example of identifiers with embedded periods:
test=> CREATE TABLE "a.b" ("c.d" SERIAL);
NOTICE: CREATE TABLE will create implicit sequence "a.b_c.d_seq" for serial column "a.b.c.d"
CREATE TABLE
test=> SELECT pg_get_serial_sequence('a.b', 'c.d');
ERROR: schema "a" does not exist
test=> SELECT pg_get_serial_sequence('"a.b"', 'c.d');
pg_get_serial_sequence
------------------------
PUBLIC."a.b_c.d_seq"
(1 row)
test=> SELECT pg_get_serial_sequence('"a.b"', '"c.d"');
ERROR: column ""c.""d of relation "a.b" does not exist
--
Bruce Momjian bruce@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: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.352
diff -c -c -r1.352 func.sgml
*** doc/src/sgml/func.sgml 20 Jan 2007 21:59:34 -0000 1.352
--- doc/src/sgml/func.sgml 30 Jan 2007 02:16:17 -0000
***************
*** 9892,9903 ****
</para>
<para>
! <function>pg_get_serial_sequence</function> fetches the name of the
! sequence associated with a column, or NULL if there is no sequence
! associated with the column. The result is suitably formatted for passing
! to the sequence functions (see <xref linkend="functions-sequence">).
! This association can be modified or removed with <command>ALTER SEQUENCE
! OWNED BY</>. (The function probably should have been called
<function>pg_get_owned_sequence</function>; its name reflects the fact
that it's typically used with <type>serial</> or <type>bigserial</>
columns.)
--- 9892,9909 ----
</para>
<para>
! <function>pg_get_serial_sequence</function> returns the name of the
! sequence associated with a column, or NULL if no sequence is associated
! with the column. The first input parameter is a table name with
! optional schema, and the second parameter is a column name. Because
! the first parameter is potentially a schema and table, it is not treated
! as a double-quoted identifier, meaning it is lowercased by default,
! while the second parameter, being just a column name, is treated as
! double-quoted and has its case preserved. The function returns a value
! suitably formatted for passing to the sequence functions (see <xref
! linkend="functions-sequence">). This association can be modified or
! removed with <command>ALTER SEQUENCE OWNED BY</>. (The function
! probably should have been called
<function>pg_get_owned_sequence</function>; its name reflects the fact
that it's typically used with <type>serial</> or <type>bigserial</>
columns.)
Index: src/backend/utils/adt/ruleutils.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.246
diff -c -c -r1.246 ruleutils.c
*** src/backend/utils/adt/ruleutils.c 25 Jan 2007 04:17:46 -0000 1.246
--- src/backend/utils/adt/ruleutils.c 30 Jan 2007 02:16:19 -0000
***************
*** 1265,1270 ****
--- 1265,1272 ----
* pg_get_serial_sequence
* Get the name of the sequence used by a serial column,
* formatted suitably for passing to setval, nextval or currval.
+ * First parameter is not treated as double-quoted, second parameter
+ * is --- see documentation for reason.
*/
Datum
pg_get_serial_sequence(PG_FUNCTION_ARGS)