Examples with PgType org.jooq.meta.postgres.pg_catalog.tables.PgType used on opensource projects

Example 1 with PgType

use of org.jooq.meta.postgres.pg_catalog.tables.PgType in project jOOQ by jOOQ.

the class PostgresDatabase method getDomains0.

@Override
protected List<DomainDefinition> getDomains0() throws SQLException {
    List<DomainDefinition> result = new ArrayList<>();
    if (existAll(PG_CONSTRAINT, PG_TYPE)) {
        PgNamespace n = PG_NAMESPACE.as("n");
        PgConstraint c = PG_CONSTRAINT.as("c");
        PgType d = PG_TYPE.as("d");
        PgType b = PG_TYPE.as("b");
        Field<String[]> src = field(name("domains", "src"), String[].class);
        Field<String> constraintDef = field("pg_get_constraintdef({0})", VARCHAR, c.OID);
        for (Record record : create().withRecursive("domains", "domain_id", "base_id", "typbasetype", "src").as(select(d.OID, d.OID, d.TYPBASETYPE, when(c.OID.isNotNull(), array(constraintDef))).from(d).join(n).on(n.OID.eq(d.TYPNAMESPACE)).leftJoin(c).on(d.OID.eq(c.CONTYPID)).where(d.TYPTYPE.eq("d")).and(n.NSPNAME.in(getInputSchemata())).unionAll(select(field(name("domains", "domain_id"), Long.class), d.OID, d.TYPBASETYPE, decode().when(c.CONBIN.isNull(), src).otherwise(arrayAppend(src, constraintDef))).from(name("domains")).join(d).on(field(name("domains", d.TYPBASETYPE.getName())).eq(d.OID)).leftJoin(c).on(d.OID.eq(c.CONTYPID)))).select(n.NSPNAME, d.TYPNAME, d.TYPNOTNULL, d.TYPDEFAULT, b.TYPNAME, // See https://github.com/postgres/postgres/blob/master/src/backend/catalog/information_schema.sql
        field("information_schema._pg_char_max_length({0}, {1})", INTEGER, d.TYPBASETYPE, d.TYPTYPMOD).as(DOMAINS.CHARACTER_MAXIMUM_LENGTH), field("information_schema._pg_numeric_precision({0}, {1})", INTEGER, d.TYPBASETYPE, d.TYPTYPMOD).as(DOMAINS.NUMERIC_PRECISION), field("information_schema._pg_numeric_scale({0}, {1})", INTEGER, d.TYPBASETYPE, d.TYPTYPMOD).as(DOMAINS.NUMERIC_SCALE), src).from(d).join(name("domains")).on(field(name("domains", "typbasetype")).eq(0)).and(field(name("domains", "domain_id")).eq(d.OID)).join(b).on(field(name("domains", "base_id")).eq(b.OID)).join(n).on(n.OID.eq(d.TYPNAMESPACE)).where(d.TYPTYPE.eq("d")).and(n.NSPNAME.in(getInputSchemata())).orderBy(n.NSPNAME, d.TYPNAME)) {
            SchemaDefinition schema = getSchema(record.get(n.NSPNAME));
            DataTypeDefinition baseType = new DefaultDataTypeDefinition(this, schema, record.get(b.TYPNAME), record.get(DOMAINS.CHARACTER_MAXIMUM_LENGTH), record.get(DOMAINS.NUMERIC_PRECISION), record.get(DOMAINS.NUMERIC_SCALE), !record.get(d.TYPNOTNULL, boolean.class), record.get(d.TYPDEFAULT), name(record.get(n.NSPNAME), record.get(b.TYPNAME)));
            DefaultDomainDefinition domain = new DefaultDomainDefinition(schema, record.get(d.TYPNAME), baseType);
            domain.addCheckClause(record.get(src));
            result.add(domain);
        }
    }
    return result;
}

Example 2 with PgType

use of org.jooq.meta.postgres.pg_catalog.tables.PgType in project jOOQ by jOOQ.

the class PostgresMaterializedViewDefinition method getElements0.

@Override
public List<ColumnDefinition> getElements0() throws SQLException {
    List<ColumnDefinition> result = new ArrayList<>();
    Columns col = COLUMNS;
    PgAttribute a = PG_ATTRIBUTE.as("a");
    PgAttrdef ad = PG_ATTRDEF.as("ad");
    PgType t = PG_TYPE.as("t");
    PgType bt = PG_TYPE.as("bt");
    PgClass c = PG_CLASS.as("c");
    PgCollation co = PG_COLLATION.as("co");
    PgNamespace nt = PG_NAMESPACE.as("nt");
    PgNamespace nc = PG_NAMESPACE.as("nc");
    PgNamespace nbt = PG_NAMESPACE.as("nbt");
    PgNamespace nco = PG_NAMESPACE.as("nco");
    for (Record record : create().select(field("({0})::information_schema.sql_identifier", col.COLUMN_NAME.getDataType(), a.ATTNAME).as(col.COLUMN_NAME), field("({0})::information_schema.cardinal_number", col.ORDINAL_POSITION.getDataType(), a.ATTNUM).as(col.ORDINAL_POSITION), field("({0})::information_schema.character_data", col.DATA_TYPE.getDataType(), when(t.TYPTYPE.eq(inline("d")), when(bt.TYPELEM.ne(inline(0L)).and(bt.TYPLEN.eq(inline((short) -1))), inline("ARRAY")).when(nbt.NSPNAME.eq(inline("pg_catalog")), field("format_type({0}, NULL::integer)", String.class, t.TYPBASETYPE)).otherwise(inline("USER-DEFINED"))).otherwise(when(t.TYPELEM.ne(inline(0L)).and(t.TYPLEN.eq(inline((short) -1))), inline("ARRAY")).when(nt.NSPNAME.eq(inline("pg_catalog")), field("format_type({0}, NULL::integer)", String.class, a.ATTTYPID)).otherwise(inline("USER-DEFINED")))).as(col.DATA_TYPE), field("(information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number", col.CHARACTER_MAXIMUM_LENGTH.getDataType()).as(col.CHARACTER_MAXIMUM_LENGTH), field("(information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number", col.NUMERIC_PRECISION.getDataType()).as(col.NUMERIC_PRECISION), field("(information_schema._pg_numeric_scale(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*)))::information_schema.cardinal_number", col.NUMERIC_SCALE.getDataType()).as(col.NUMERIC_SCALE), field("({0})::information_schema.yes_or_no", col.IS_NULLABLE.getDataType(), when(condition(a.ATTNOTNULL).or(t.TYPTYPE.eq(inline("d")).and(t.TYPNOTNULL)), inline("NO")).otherwise(inline("YES"))).as(col.IS_NULLABLE), field("(pg_get_expr({0}, {1}))::information_schema.character_data", col.COLUMN_DEFAULT.getDataType(), ad.ADBIN, ad.ADRELID).as(col.COLUMN_DEFAULT), field("({0})::information_schema.sql_identifier", col.UDT_SCHEMA.getDataType(), nvl(nbt.NSPNAME, nt.NSPNAME)).as(col.UDT_SCHEMA), field("({0})::information_schema.sql_identifier", col.UDT_NAME.getDataType(), nvl(bt.TYPNAME, t.TYPNAME)).as(col.UDT_NAME), PG_DESCRIPTION.DESCRIPTION).from(a.leftJoin(ad).on(a.ATTRELID.eq(ad.ADRELID)).and(a.ATTNUM.eq(ad.ADNUM)).join(c.join(nc).on(c.RELNAMESPACE.eq(nc.OID))).on(a.ATTRELID.eq(c.OID)).join(t.join(nt).on(t.TYPNAMESPACE.eq(nt.OID))).on(a.ATTTYPID.eq(t.OID))).leftJoin(bt.join(nbt).on(bt.TYPNAMESPACE.eq(nbt.OID))).on(t.TYPTYPE.eq(inline("d")).and(t.TYPBASETYPE.eq(bt.OID))).leftJoin(co.join(nco).on(co.COLLNAMESPACE.eq(nco.OID))).on(a.ATTCOLLATION.eq(co.OID).and(nco.NSPNAME.ne(inline("pg_catalog")).or(co.COLLNAME.ne(inline("default"))))).leftJoin(PG_DESCRIPTION).on(PG_DESCRIPTION.OBJOID.eq(c.OID)).and(PG_DESCRIPTION.OBJSUBID.eq(a.ATTNUM.coerce(PG_DESCRIPTION.OBJSUBID))).where(not(condition("pg_is_other_temp_schema({0})", nc.OID)).and(a.ATTNUM.gt(inline((short) 0))).and(not(a.ATTISDROPPED)).and(c.RELKIND.eq(inline("m"))).and(nc.NSPNAME.in(getSchema().getName())).and(c.RELNAME.eq(getName()))).orderBy(a.ATTNUM)) {
        SchemaDefinition typeSchema = null;
        String schemaName = record.get(COLUMNS.UDT_SCHEMA);
        if (schemaName != null)
            typeSchema = getDatabase().getSchema(schemaName);
        DataTypeDefinition type = new DefaultDataTypeDefinition(getDatabase(), typeSchema, record.get(COLUMNS.DATA_TYPE), record.get(COLUMNS.CHARACTER_MAXIMUM_LENGTH), record.get(COLUMNS.NUMERIC_PRECISION), record.get(COLUMNS.NUMERIC_SCALE), record.get(COLUMNS.IS_NULLABLE, boolean.class), record.get(COLUMNS.COLUMN_DEFAULT), name(record.get(COLUMNS.UDT_SCHEMA), record.get(COLUMNS.UDT_NAME)));
        result.add(new DefaultColumnDefinition(getDatabase().getTable(getSchema(), getName()), record.get(COLUMNS.COLUMN_NAME), result.size() + 1, type, defaultString(record.get(COLUMNS.COLUMN_DEFAULT)).startsWith("nextval"), record.get(PG_DESCRIPTION.DESCRIPTION)));
    }
    return result;
}

Example 3 with PgType

use of org.jooq.meta.postgres.pg_catalog.tables.PgType in project jOOQ by jOOQ.

the class PostgresTableValuedFunction method getElements0.

@Override
public List<ColumnDefinition> getElements0() throws SQLException {
    List<ColumnDefinition> result = new ArrayList<>();
    Routines r = ROUTINES;
    Parameters p = PARAMETERS;
    PgNamespace pg_n = PG_NAMESPACE;
    PgProc pg_p = PG_PROC;
    Columns c = COLUMNS;
    PgType pg_t = PG_TYPE;
    Field<Integer> pPrecision = pNumericPrecision(p);
    Field<Integer> cPrecision = nvl(c.DATETIME_PRECISION, c.NUMERIC_PRECISION);
    Field<Integer> rPrecision = nvl(r.DATETIME_PRECISION, r.NUMERIC_PRECISION);
    for (Record record : create().select(p.PARAMETER_NAME, rowNumber().over(partitionBy(p.SPECIFIC_NAME).orderBy(p.ORDINAL_POSITION)).as(p.ORDINAL_POSITION), p.DATA_TYPE, p.CHARACTER_MAXIMUM_LENGTH, pPrecision.as(p.NUMERIC_PRECISION), p.NUMERIC_SCALE, inline("true").as(c.IS_NULLABLE), (((PostgresDatabase) getDatabase()).is94() ? PARAMETERS.PARAMETER_DEFAULT : inline((String) null)).as(c.COLUMN_DEFAULT), p.UDT_SCHEMA, p.UDT_NAME).from(r).join(p).on(row(r.SPECIFIC_CATALOG, r.SPECIFIC_SCHEMA, r.SPECIFIC_NAME).eq(p.SPECIFIC_CATALOG, p.SPECIFIC_SCHEMA, p.SPECIFIC_NAME)).join(pg_n).on(r.SPECIFIC_SCHEMA.eq(pg_n.NSPNAME)).join(pg_p).on(pg_p.PRONAMESPACE.eq(pg_n.OID)).and(pg_p.PRONAME.concat("_").concat(pg_p.OID).eq(r.SPECIFIC_NAME)).where(r.SPECIFIC_NAME.eq(specificName)).and(p.PARAMETER_MODE.ne("IN")).and(pg_p.PRORETSET).unionAll(// from INFORMATION_SCHEMA.TABLES
    select(coalesce(c.COLUMN_NAME, getName()).as(c.COLUMN_NAME), coalesce(c.ORDINAL_POSITION, inline(1)).as(c.ORDINAL_POSITION), coalesce(c.DATA_TYPE, r.DATA_TYPE).as(c.DATA_TYPE), coalesce(c.CHARACTER_MAXIMUM_LENGTH, r.CHARACTER_MAXIMUM_LENGTH).as(c.CHARACTER_MAXIMUM_LENGTH), coalesce(cPrecision, rPrecision).as(c.NUMERIC_PRECISION), coalesce(c.NUMERIC_SCALE, r.NUMERIC_SCALE).as(c.NUMERIC_SCALE), coalesce(c.IS_NULLABLE, "true").as(c.IS_NULLABLE), coalesce(c.COLUMN_DEFAULT, inline((String) null)).as(c.COLUMN_DEFAULT), coalesce(c.UDT_SCHEMA, inline((String) null)).as(c.UDT_SCHEMA), coalesce(c.UDT_NAME, r.UDT_NAME, field(select(pg_t.TYPNAME).from(pg_t).where(pg_t.OID.eq(pg_p.PRORETTYPE)))).as(c.UDT_NAME)).from(r).leftOuterJoin(c).on(row(r.TYPE_UDT_CATALOG, r.TYPE_UDT_SCHEMA, r.TYPE_UDT_NAME).eq(c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME)).join(pg_n).on(r.SPECIFIC_SCHEMA.eq(pg_n.NSPNAME)).join(pg_p).on(pg_p.PRONAMESPACE.eq(pg_n.OID)).and(pg_p.PRONAME.concat("_").concat(pg_p.OID).eq(r.SPECIFIC_NAME)).where(r.SPECIFIC_NAME.eq(specificName)).and(row(r.SPECIFIC_CATALOG, r.SPECIFIC_SCHEMA, r.SPECIFIC_NAME).notIn(select(p.SPECIFIC_CATALOG, p.SPECIFIC_SCHEMA, p.SPECIFIC_NAME).from(p).where(p.PARAMETER_MODE.eq("OUT")))).and(pg_p.PRORETSET)).orderBy(2)) {
        SchemaDefinition typeSchema = null;
        String schemaName = record.get(p.UDT_SCHEMA);
        if (schemaName != null)
            typeSchema = getDatabase().getSchema(schemaName);
        DataTypeDefinition type = new DefaultDataTypeDefinition(getDatabase(), typeSchema, record.get(p.DATA_TYPE), record.get(p.CHARACTER_MAXIMUM_LENGTH), record.get(p.NUMERIC_PRECISION), record.get(p.NUMERIC_SCALE), record.get(c.IS_NULLABLE, boolean.class), record.get(c.COLUMN_DEFAULT), name(record.get(p.UDT_SCHEMA), record.get(p.UDT_NAME)));
        result.add(new DefaultColumnDefinition(getDatabase().getTable(getSchema(), getName()), record.get(p.PARAMETER_NAME), result.size() + 1, type, defaultString(record.get(c.COLUMN_DEFAULT)).startsWith("nextval"), null));
    }
    return result;
}