Re: [HACKERS] [BUGS] BUG #2907: pg_get_serial_sequence quoting - Mailing list pgsql-patches

From Bruce Momjian
Subject Re: [HACKERS] [BUGS] BUG #2907: pg_get_serial_sequence quoting
Date
Msg-id 200701300234.l0U2Yr423647@momjian.us
Whole thread Raw
Responses Re: [HACKERS] [BUGS] BUG #2907: pg_get_serial_sequence quoting  (Adriaan van Os <postgres@microbizz.nl>)
List pgsql-patches
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)

pgsql-patches by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: [ADMIN] server process (PID xxx) was
Next
From: "Pavan Deolasee"
Date:
Subject: Lock compatibility matrix