Add support for IDENTITY columns

[theory]

From the discussion, perhaps we also want to add functions to test whether a column is STORED or GENERATED, as well. See especially this followup with some useful catalog querying context:

-- a function for testing since pgtap doesn't have an equivalent of this
-- remove when pgtap can check for column identities
CREATE OR REPLACE FUNCTION public._get_column_identity(
    p_schema    VARCHAR,
    p_table     VARCHAR,
    p_column    VARCHAR
    )
    RETURNS CHAR AS $$
        SELECT coalesce(a.attidentity, a.attgeneratd)
        FROM pg_catalog.pg_attribute a
        WHERE a.attrelid =
            ( SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
              WHERE n.nspname = p_schema AND c.relname = p_table )
          AND a.attnum > 0
          AND NOT a.attisdropped
          AND a.attname = p_column
          AND pg_catalog.format_type(a.atttypid, a.atttypmod) in ( 'integer', 'smallinteger', 'biginteger' )
          AND a.attnotnull ;
    $$ LANGUAGE SQL;

[kbrannen]

I think the path to take is:

col_identity_is(:schema, :table, :column, :type [, :desc]);
col_identity_options_are(:schema, :table, :column, ARRAY[ :options ] [, :desc])
col_identity_expression_is(:schema, :table, :column, :expression [, :desc])

At present, I think I'll split the work into 2 parts. Part 1 is the first function as that's the most bang for the buck in many ways. Part 2 will be the last 2 functions since they won't be used as much.

BTW, schema is required as we need that to get the OID of the column we're working on, so I don't think we can have a "schema-less" version of these functions.

If you look at the docs for "CREATE TABLE", this is documented as a column constraint (attribute) and as: GENERATED ALWAYS AS ( generation_expr ) STORED -- or -- GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

-- The generation expression can refer to other columns in the table, but not other generated columns. Any functions and operators used must be immutable. References to other tables are not allowed. -- For sequence_options see doc for CREATE SEQUENCE -- It creates an implicit sequence, which means that it can only occur on SMALLINT, INT, or BIGINT type columns.

The first function would be something in the direction of:

CREATE OR REPLACE FUNCTION col_identity_is(
    p_schema    VARCHAR,
    p_table     VARCHAR,
    p_column    VARCHAR,
    p_type      VARCHAR, -- 'always','default','stored'
    p_desc      VARCHAR  -- could allow to be optional
    )
    RETURNS BOOLEAN AS $$
    DECLARE
        v_type: CHAR;
        v_expr: VARCHAR;
    BEGIN
        /*
        -- might need to change the function to return the expression too even if we don't use it  here
        SELECT _get_column_identity(p_schema, p_table, p_column) INTO v_type, v_expr;

        if v_type is null, then throw an exception about col not having identity
        case statement to convert lower(p_type) to a char for comparison
        if v_type != p_type, then throw an exception about not matching type
        return true;
        */
    END;
    $$ LANGUAGE PLPGSQL;

I need to rework the psuedo-code for the generated_expression and sequence_options since I had them combined into one; I'll add that later. Notes:

• Probably need to remove all spaces in the generated_expression before comparing to make that easier to match.
• For the sequence_options, that probably should be an array, then we'd need to compare that to the array in the catalog table somehow. (Does pgTap already do this since it already knows about sequences? Check to see.)

[kbrannen]

From the email thread, David suggests col_identity_type_is(). That's works for me.