SQL2003 GENERATED ... AS ... syntax - Mailing list pgsql-hackers

As previously mentioned, I'm working on implementing [subject].  I
think I've mostly worked it out, but I'm having trouble with the
GENERATED ALWAYS case.

My changes (see attached patch) can be summarized as follows:

 - in backend/utils/adt/misc.c:

   - added a force_default_value() function which takes a string
     argument (the name of the column to force to default) and
     currently does nothing.

 - in backend/parser/gram.y:

   - when GENERATED ... AS ... is encountered in a column definition,
     it adds a node of the new T_Generated type to the constraint
     list.  This node contains a bool that differentiates between BY
     DEFAULT and ALWAYS, and a pointer to a CreateSeqStmt (for
     IDENTITY '(' OptSeqList ')') or a List constructed by the a_expr
     production (for '(' a_expr ')')

 - in backend/parser/analyze.c:

   - factored out the code from transformColumnDefinition() that
     creates the sequence and the DEFAULT constraint into a separate
     CreateSerialColumn() function which takes as one of its arguments
     is a List of sequence options.  The SERIAL code passes in a NIL
     list, while the GENERATED AS IDENTITY code passes in the option
     list from the CreateSeqStmt.

   - added a CreateAlwaysDefaultColumn() function which synthesizes a
     CreateTrigStmt equivalent to CREATE TRIGGER foo BEFORE INSERT ON
     bar FOR EACH ROW EXECUTE PROCEDURE force_default_value ('baz')
     and adds it to the work list.  This function is called by
     transformColumnDefinition() if a Generated node with always set
     to true is encountered.

Now I must be doing something wrong in CreateAlwaysDefaultColumn(),
because the CreateTrigStmt fails to execute:

| des=# create table test ( id int generated always as identity ( minvalue 1000 ), word text );
| NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for SERIAL column "test.id"
| NOTICE:  CREATE TABLE will create implicit trigger "test_id_always_default" for column "test.id"
| ERROR:  relation "public.test" does not exist

GENERATED BY DEFAULT AS IDENTITY works fine though, so I must have
done *something* right:

| des=# create table test ( id int generated by default as identity ( minvalue 1000 ), word text );
| NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for SERIAL column "test.id"
| CREATE TABLE
| des=# select sequence_name, last_value, min_value, max_value from test_id_seq;
|  sequence_name | last_value | min_value |      max_value
| ---------------+------------+-----------+---------------------
|  test_id_seq   |       1000 |      1000 | 9223372036854775807
| (1 row)
|

On the other hand, I seem to have botched the definition of
force_default_value() in include/catalog/pg_proc.h, because adding the
trigger manually doesn't seem to work either:

| des=# \df force_default_value
|                              List of functions
|  Result data type |   Schema   |        Name         | Argument data types
| ------------------+------------+---------------------+---------------------
|  "trigger"        | pg_catalog | force_default_value | text
| (1 row)
|
| des=# create trigger test_id_always_default before insert on test for each row execute procedure force_default_value
('id');
| ERROR:  function force_default_value() does not exist

Any suggestions?

DES
--
Dag-Erling Smørgrav - des@des.no