@Override public ResultSet getSchemas(String catalog, String schemaPattern) throws SQLException { String sql; sql = "SELECT nspname AS TABLE_SCHEM, NULL AS TABLE_CATALOG FROM pg_catalog.pg_namespace " + " WHERE nspname <> 'pg_toast' AND (nspname !~ '^pg_temp_' " + " OR nspname = (pg_catalog.current_schemas(true))[1]) AND (nspname !~ '^pg_toast_temp_' " + " OR nspname = replace((pg_catalog.current_schemas(true))[1], 'pg_temp_', 'pg_toast_temp_')) "; if (schemaPattern != null && !schemaPattern.isEmpty()) { sql += " AND nspname LIKE " + escapeQuotes(schemaPattern); } sql += " ORDER BY TABLE_SCHEM"; return createMetaDataStatement().executeQuery(sql); }
public ResultSet getProcedures(String catalog, String schemaPattern, String procedureNamePattern) throws SQLException { String sql; sql = "SELECT NULL AS PROCEDURE_CAT, n.nspname AS PROCEDURE_SCHEM, p.proname AS PROCEDURE_NAME, " + "NULL, NULL, NULL, d.description AS REMARKS, " + DatabaseMetaData.procedureReturnsResult + " AS PROCEDURE_TYPE, " + " p.proname || '_' || p.oid AS SPECIFIC_NAME " + " FROM pg_catalog.pg_namespace n, pg_catalog.pg_proc p " + " LEFT JOIN pg_catalog.pg_description d ON (p.oid=d.objoid) " + " LEFT JOIN pg_catalog.pg_class c ON (d.classoid=c.oid AND c.relname='pg_proc') " + " LEFT JOIN pg_catalog.pg_namespace pn ON (c.relnamespace=pn.oid AND pn.nspname='pg_catalog') " + " WHERE p.pronamespace=n.oid "; if (schemaPattern != null && !schemaPattern.isEmpty()) { sql += " AND n.nspname LIKE " + escapeQuotes(schemaPattern); } if (procedureNamePattern != null && !procedureNamePattern.isEmpty()) { sql += " AND p.proname LIKE " + escapeQuotes(procedureNamePattern); } sql += " ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME, p.oid::text "; return createMetaDataStatement().executeQuery(sql); }
public ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException { String sql; sql = "SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, " + " ct.relname AS TABLE_NAME, a.attname AS COLUMN_NAME, " + " (i.keys).n AS KEY_SEQ, ci.relname AS PK_NAME " + "FROM pg_catalog.pg_class ct " + " JOIN pg_catalog.pg_attribute a ON (ct.oid = a.attrelid) " + " JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid) " + " JOIN (SELECT i.indexrelid, i.indrelid, i.indisprimary, " + " information_schema._pg_expandarray(i.indkey) AS keys " + " FROM pg_catalog.pg_index i) i " + " ON (a.attnum = (i.keys).x AND a.attrelid = i.indrelid) " + " JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid) " + "WHERE true "; if (schema != null && !schema.isEmpty()) { sql += " AND n.nspname = " + escapeQuotes(schema); } if (table != null && !table.isEmpty()) { sql += " AND ct.relname = " + escapeQuotes(table); } sql += " AND i.indisprimary " + " ORDER BY table_name, pk_name, key_seq"; return createMetaDataStatement().executeQuery(sql); }
+ "WHERE pg_function_is_visible(p.oid) "; if (schemaPattern != null && !schemaPattern.isEmpty()) { sql += " AND n.nspname LIKE " + escapeQuotes(schemaPattern); sql += " AND p.proname LIKE " + escapeQuotes(functionNamePattern);
select += " AND n.nspname LIKE " + escapeQuotes(schemaPattern); select += " AND c.relname LIKE " + escapeQuotes(tableNamePattern);
sql += " AND n.nspname = " + escapeQuotes(schema); where += " AND n.nspname = " + escapeQuotes(schema); sql += " AND ct.relname = " + escapeQuotes(tableName);
String pgType = i.next(); int sqlType = connection.getTypeInfo().getSQLType(pgType); sqlwhen.append(" when typname = ").append(escapeQuotes(pgType)).append(" then ").append(sqlType); toAdd.append(" and t.typname like ").append(escapeQuotes(typeNamePattern)); toAdd.append(" and n.nspname like ").append(escapeQuotes(schemaPattern));
sql += " AND pkn.nspname = " + escapeQuotes(primarySchema); sql += " AND fkn.nspname = " + escapeQuotes(foreignSchema); sql += " AND pkc.relname = " + escapeQuotes(primaryTable); sql += " AND fkc.relname = " + escapeQuotes(foreignTable);
sql += " AND n.nspname = " + escapeQuotes(schema); sql += " AND c.relname = " + escapeQuotes(table); sql += " AND a.attname LIKE " + escapeQuotes(columnNamePattern);
sql += " AND n.nspname LIKE " + escapeQuotes(schemaPattern); sql += " AND c.relname LIKE " + escapeQuotes(tableNamePattern);
sql += " AND n.nspname = " + escapeQuotes(schema); sql += " AND ct.relname = " + escapeQuotes(table) + " AND i.indisprimary " + " ORDER BY a.attnum ";
+ " WHERE p.pronamespace=n.oid AND p.prorettype=t.oid "; if (schemaPattern != null && !schemaPattern.isEmpty()) { sql += " AND n.nspname LIKE " + escapeQuotes(schemaPattern); sql += " AND p.proname LIKE " + escapeQuotes(procedureNamePattern);
sql += " AND n.nspname LIKE " + escapeQuotes(schemaPattern); sql += " AND c.relname LIKE " + escapeQuotes(tableNamePattern); sql += " AND attname LIKE " + escapeQuotes(columnNamePattern);