/// <summary> /// Write the different create indeces/key constraint statements. /// </summary> /// <param name="conn"></param> private static void GenerateBuildIndexes(NpgsqlConnection conn) { var indexPath = Path.Combine( _params[Parameters.OtherWorkPath].ToString(), Constants.CreateIndexesEtAl); // option column values: // INDOPTION_DESC 0x0001 = values are in reverse order (DESC) // INDOPTION_NULLS_FIRST 0x0002 = NULLs are first instead of last const string sql = @"SELECT n.nspname AS schema_name, c.relname AS table_name, d.relname AS index_name, i.indisprimary, i.indisunique, a.attname AS column_name, i.indoption[a.attnum - 1] as option FROM pg_index i LEFT JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY ( i.indkey ) LEFT JOIN pg_class c ON c.oid = i.indrelid LEFT JOIN pg_class d ON d.oid = i.indexrelid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname NOT IN('pg_toast', 'pg_catalog', 'information_schema', 'public') ORDER BY n.nspname ASC, c.relname ASC, d.relname ASC, a.attnum"; using (var cmd = new NpgsqlCommand(sql, conn)) using (var sw = new StreamWriter(indexPath, false, Encoding.Default)) { sw.WriteBeginTrans(); var savedSchema = ""; var savedTable = ""; var savedIndex = ""; var savedType = ' '; using (var reader = cmd.ExecuteReader()) { var sb = new StringBuilder(); while (reader.Read()) { var schema = reader["schema_name"].ToString(); var table = reader["table_name"].ToString(); var index = reader["index_name"].ToString(); // Schema, table or index changed: close index defenition. if (!savedSchema.Equals(schema) || !savedTable.Equals(table) || !savedIndex.Equals(index)) { if (sb.Length > 0) sw.WriteIndex( savedSchema, savedTable, savedIndex, savedType, sb.ToString()); sb.Clear(); savedSchema = schema; savedTable = table; savedIndex = index; if ((bool) reader["indisprimary"]) { savedType = 'P'; } else if ((bool) reader["indisunique"]) { savedType = 'U'; } else savedType = 'I'; } else sb.Append(", "); sb.Append("[" + reader["column_name"] + "]"); if (reader["option"] == DBNull.Value) continue; switch ((short) reader["option"]) { case 0: sb.Append(" ASC"); break; case 1: sb.Append(" DESC"); break; } } if (sb.Length > 0) sw.WriteIndex( savedSchema, savedTable, savedIndex, savedType, sb.ToString()); sw.WriteCommitTrans(); } } }