Thread: Rough idea for supporting "sequencename.nextval" syntax

I have what may be a half-baked idea for allowing nextval and friends to
work with a true sequence-name parameter, rather than a string
equivalent.

Suppose that we invent a new datatype "regclass", similar to regproc:
it's actually an OID, but it has the additional implication that it is
the OID of a pg_class row, and the I/O operations for the type try to
accept or print a class name not just a numeric OID.

Next, hack the parser to understand that when a function has an argument
declared as type regclass and is invoked with the syntax relname.func or
func(relname), what is wanted is for the OID of the relation to be
passed as a constant argument; the relation is NOT inserted into the
query's rangetable.

Then, it's a simple matter to write a variant of nextval that identifies
its target sequence by OID rather than name.  The function will still be
responsible for ensuring that what it's pointed at is indeed a sequence,
since the parser won't enforce that.

I haven't yet studied the parser to see how much of a hack this would
be, but it seems doable.  The facility might be of use for other
functions besides the sequence ones, too.

Thoughts?
        regards, tom lane

I said:
> Suppose that we invent a new datatype "regclass", similar to regproc:
> it's actually an OID, but it has the additional implication that it is
> the OID of a pg_class row, and the I/O operations for the type try to
> accept or print a class name not just a numeric OID.

> Next, hack the parser to understand that when a function has an argument
> declared as type regclass and is invoked with the syntax relname.func or
> func(relname), what is wanted is for the OID of the relation to be
> passed as a constant argument; the relation is NOT inserted into the
> query's rangetable.

> Then, it's a simple matter to write a variant of nextval that identifies
> its target sequence by OID rather than name.

Actually, there'd be no need to have two versions of nextval().
Consider what happens when you write:
select nextval('foo');

'foo' is an unknown-type literal, so if the only available function
nextval is one that takes "regclass", guess what happens: 'foo' is fed
to the input conversion routine for regclass.  Given the above proposal,
the result would be the OID for sequence foo, and away we go.

Interestingly, this'd result in an automatic upgrade path for nextval
calls: an expression like nextval('foo') would be parsed into the same
expression tree as nextval(foo), and with appropriate smarts in
ruleutils.c, it'd get listed that way in your next pg_dump.

There might be some value in continuing to accept "text" input for
nextval, for example to supportselect nextval('tabname' || 'seqname' || '_seq');
which seems like a plausible thing for someone to do.  My inclination
would be to handle this by defining a text-to-regclass conversion
function, and still have just one nextval().

This is starting to seem less like a kluge and more like a real
feature...
        regards, tom lane

On Thu, Aug 16, 2001 at 10:36:49PM -0400, Tom Lane wrote:
> I said:
> > Suppose that we invent a new datatype "regclass", similar to regproc:
> > it's actually an OID, but it has the additional implication that it is
> > the OID of a pg_class row, and the I/O operations for the type try to
> > accept or print a class name not just a numeric OID.

Tom, would it make sense to use this new type in the system tables where
pg_class oids currently are used, such as pg_attribute.attrelid ? 

Then, one could do:

select attname from pg_attributes where attrelid = 'mytablename';

If the appropriate type conversions where in place. (I just tried this
with pg_aggregate, looking for aggregates that use a particular operator,
and failed, since text(<some regproc>) yields the oid, rather than
the name.)

This would essentially special case the join of two system tables.  Hmm,
sounds like a step down the trail to not needing user visible oids for
system tables, even. 
> This is starting to seem less like a kluge and more like a real
> feature...

Ross

"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> Tom, would it make sense to use this new type in the system tables where
> pg_class oids currently are used, such as pg_attribute.attrelid ? 

Probably.  We already use regproc where pg_proc OIDs are used --- not
completely consistently IIRC, but it'd be good to be more consistent.

> Then, one could do:
> select attname from pg_attributes where attrelid = 'mytablename';

> If the appropriate type conversions where in place. (I just tried this
> with pg_aggregate, looking for aggregates that use a particular operator,
> and failed, since text(<some regproc>) yields the oid, rather than
> the name.)

Good thought.  At the moment an explicit cast is needed for regproc,
and probably the same would be true of regclass unless we did some
further hacking:

regression=# select * from pg_aggregate where aggfinalfn = 'interval_avg';
ERROR:  oidin: error in "interval_avg": can't parse "interval_avg"
regression=# select * from pg_aggregate where aggfinalfn = 'interval_avg'::regproc;aggname | aggowner |   aggtransfn
| aggfinalfn  | aggbasetype | aggtranstype | aggfinaltype |     agginitval
 

---------+----------+----------------+--------------+-------------+--------------+--------------+---------------------avg
   |      256 | interval_accum | interval_avg |        1186 |         1187 |         1186 | {0 second,0 second}
 
(1 row)

I think the reason the literal is resolved as OID not regproc is that we
are using the OID equality operator here (relying on binary equivalence
of OID and regproc).  I don't much want to invent a whole set of regproc
and regclass operators to avoid that.  Perhaps the unknown-type
resolution rules could be fine-tuned somehow to resolve as the type of
the other operand, rather than the declared input type of the operator,
in cases like this.  (Thomas, any thoughts about that?)

Looking at this, I can't help wondering about "regtype" too ...
        regards, tom lane