Examples with ROUTINES org.jooq.meta.postgres.information_schema.Tables.ROUTINES used on opensource projects

Example 1 with ROUTINES

use of org.jooq.meta.postgres.information_schema.Tables.ROUTINES in project jOOQ by jOOQ.

the class PostgresDatabase method getRoutines0.

@Override
protected List<RoutineDefinition> getRoutines0() throws SQLException {
    List<RoutineDefinition> result = new ArrayList<>();
    if (!canUseRoutines())
        return result;
    Routines r1 = ROUTINES.as("r1");
    // [#7785] The pg_proc.proisagg column has been replaced incompatibly in PostgreSQL 11
    Field<Boolean> isAgg = (is11() ? field(PG_PROC.PROKIND.eq(inline("a"))) : field("{0}.proisagg", SQLDataType.BOOLEAN, PG_PROC)).as("is_agg");
    return create().select(r1.ROUTINE_SCHEMA, r1.ROUTINE_NAME, r1.SPECIFIC_NAME, r1.ROUTINE_TYPE, when(r1.DATA_TYPE.eq(inline("USER-DEFINED")).and(r1.TYPE_UDT_NAME.eq(inline("geometry"))), inline("geometry")).else_(canCombineArrays() ? when(condition("{0} && ARRAY['o','b']::\"char\"[]", PG_PROC.PROARGMODES), inline("void")).else_(r1.DATA_TYPE) : r1.DATA_TYPE).as("data_type"), r1.CHARACTER_MAXIMUM_LENGTH, // [#12048] TODO: Maintain whether we know the precision or not
    when(r1.NUMERIC_PRECISION.isNull().and(r1.DATA_TYPE.in(inline("time"), inline("timetz"), inline("time without time zone"), inline("time with time zone"), inline("timestamp"), inline("timestamptz"), inline("timestamp without time zone"), inline("timestamp with time zone"))), inline(6)).else_(r1.NUMERIC_PRECISION).as(r1.NUMERIC_PRECISION), r1.NUMERIC_SCALE, r1.TYPE_UDT_SCHEMA, r1.TYPE_UDT_NAME, // Calculate overload index if applicable
    when(count().over(partitionBy(r1.ROUTINE_SCHEMA, r1.ROUTINE_NAME)).gt(one()), rowNumber().over(partitionBy(r1.ROUTINE_SCHEMA, r1.ROUTINE_NAME).orderBy(// replace(field("pg_get_function_arguments({0})", VARCHAR, PG_PROC.OID), inline('"'), inline("")),
    r1.SPECIFIC_NAME))).as("overload"), isAgg).from(r1).join(PG_NAMESPACE).on(PG_NAMESPACE.NSPNAME.eq(r1.SPECIFIC_SCHEMA)).join(PG_PROC).on(PG_PROC.PRONAMESPACE.eq(PG_NAMESPACE.OID)).and(is12() ? condition("nameconcatoid({0}, {1}) = {2}", PG_PROC.PRONAME, PG_PROC.OID, r1.SPECIFIC_NAME) : PG_PROC.PRONAME.concat("_").concat(PG_PROC.OID).eq(r1.SPECIFIC_NAME)).where(r1.ROUTINE_SCHEMA.in(getInputSchemata())).and(tableValuedFunctions() ? condition(not(PG_PROC.PRORETSET)) : noCondition()).and(!getIncludeTriggerRoutines() ? r1.DATA_TYPE.isDistinctFrom(inline("trigger")) : noCondition()).orderBy(r1.ROUTINE_SCHEMA.asc(), r1.ROUTINE_NAME.asc(), field(name("overload")).asc()).collect(mapping(r -> new PostgresRoutineDefinition(this, r), Collectors.<RoutineDefinition>toList()));
}

Example 2 with ROUTINES

use of org.jooq.meta.postgres.information_schema.Tables.ROUTINES 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;
}