public static async Task <IReadOnlyList <DbObjectName> > ExistingTables(this NpgsqlConnection conn, string namePattern = null, string[] schemas = null)
{
var builder = new CommandBuilder();
builder.Append("SELECT schemaname, relname FROM pg_stat_user_tables");
if (namePattern.IsNotEmpty())
{
builder.Append(" WHERE relname like :table");
builder.AddNamedParameter("table", namePattern);
if (schemas != null)
{
builder.Append(" and schemaname = ANY(:schemas)");
builder.AddNamedParameter("schemas", schemas);
}
}
else if (schemas != null)
{
builder.Append(" WHERE schemaname = ANY(:schemas)");
builder.AddNamedParameter("schemas", schemas);
}
builder.Append(";");
return(await builder.FetchList(conn, ReadDbObjectName));
}
public static async Task <IReadOnlyList <DbObjectName> > ExistingFunctions(this NpgsqlConnection conn, string namePattern = null, string[] schemas = null)
{
var builder = new CommandBuilder();
builder.Append("SELECT specific_schema, routine_name FROM information_schema.routines WHERE type_udt_name != 'trigger'");
if (namePattern.IsNotEmpty())
{
builder.Append(" and routine_name like :name");
builder.AddNamedParameter("name", namePattern);
}
if (schemas != null)
{
builder.Append(" and specific_schema = ANY(:schemas)");
builder.AddNamedParameter("schemas", schemas);
}
builder.Append(";");
return(await builder.FetchList(conn, ReadDbObjectName));
}