public void WriteFunctionSql(PostgresUpsertType upsertType, StringWriter writer)
{
var argList = Arguments.Select(x => x.ArgumentDeclaration()).Join(", ");
var updates = Arguments.Where(x => x.Column != "id")
.Select(x => $"\"{x.Column}\" = {x.Arg}").Join(", ");
var inserts = Arguments.Select(x => $"\"{x.Column}\"").Join(", ");
var valueList = Arguments.Select(x => x.Arg).Join(", ");
if (upsertType == PostgresUpsertType.Legacy)
{
writer.WriteLine($"CREATE OR REPLACE FUNCTION {FunctionName}({argList}) RETURNS VOID AS");
writer.WriteLine("$$");
writer.WriteLine("BEGIN");
writer.WriteLine($"LOCK TABLE {TableName} IN SHARE ROW EXCLUSIVE MODE;");
writer.WriteLine($" WITH upsert AS (UPDATE {TableName} SET {updates} WHERE id=docId RETURNING *) ");
writer.WriteLine($" INSERT INTO {TableName} ({inserts})");
writer.WriteLine($" SELECT {valueList} WHERE NOT EXISTS (SELECT * FROM upsert);");
writer.WriteLine("END;");
writer.WriteLine("$$ LANGUAGE plpgsql;");
}
else
{
writer.WriteLine($"CREATE OR REPLACE FUNCTION {FunctionName}({argList}) RETURNS VOID AS");
writer.WriteLine("$$");
writer.WriteLine("BEGIN");
writer.WriteLine($"INSERT INTO {TableName} VALUES ({valueList})");
writer.WriteLine($" ON CONFLICT ON CONSTRAINT pk_{TableName}");
writer.WriteLine($" DO UPDATE SET {updates};");
writer.WriteLine("END;");
writer.WriteLine("$$ LANGUAGE plpgsql;");
}
}
public void WriteFunctionSql(PostgresUpsertType upsertType, StringWriter writer)
{
var argList = Arguments.Select(x => x.ArgumentDeclaration()).Join(", ");
var updates = Arguments.Where(x => x.Column != "id")
.Select(x => $"\"{x.Column}\" = {x.Arg}").Join(", ");
var inserts = Arguments.Select(x => $"\"{x.Column}\"").Join(", ");
var valueList = Arguments.Select(x => x.Arg).Join(", ");
if (upsertType == PostgresUpsertType.Legacy)
{
writer.WriteLine($"CREATE OR REPLACE FUNCTION {FunctionName}({argList}) RETURNS VOID AS");
writer.WriteLine("$$");
writer.WriteLine("BEGIN");
writer.WriteLine($"LOCK TABLE {TableName} IN SHARE ROW EXCLUSIVE MODE;");
writer.WriteLine($" WITH upsert AS (UPDATE {TableName} SET {updates} WHERE id=docId RETURNING *) ");
writer.WriteLine($" INSERT INTO {TableName} ({inserts})");
writer.WriteLine($" SELECT {valueList} WHERE NOT EXISTS (SELECT * FROM upsert);");
writer.WriteLine("END;");
writer.WriteLine("$$ LANGUAGE plpgsql;");
}
else
{
writer.WriteLine($"CREATE OR REPLACE FUNCTION {FunctionName}({argList}) RETURNS VOID AS");
writer.WriteLine("$$");
writer.WriteLine("BEGIN");
writer.WriteLine($"INSERT INTO {TableName} VALUES ({valueList})");
writer.WriteLine($" ON CONFLICT ON CONSTRAINT pk_{TableName}");
writer.WriteLine($" DO UPDATE SET {updates};");
writer.WriteLine("END;");
writer.WriteLine("$$ LANGUAGE plpgsql;");
}
}
public void WriteFunctionSql(PostgresUpsertType upsertType, StringWriter writer)
{
var ordered = OrderedArguments();
var argList = ordered.Select(x => x.ArgumentDeclaration()).Join(", ");
var systemUpdates = new string[] { $"{DocumentMapping.LastModifiedColumn} = transaction_timestamp()" };
var updates = ordered.Where(x => x.Column != "id")
.Select(x => $"\"{x.Column}\" = {x.Arg}").Concat(systemUpdates).Join(", ");
var inserts = ordered.Select(x => $"\"{x.Column}\"").Concat(new string[] { DocumentMapping.LastModifiedColumn }).Join(", ");
var valueList = ordered.Select(x => x.Arg).Concat(new string[] { "transaction_timestamp()" }).Join(", ");
if (upsertType == PostgresUpsertType.Legacy)
{
writer.WriteLine($"CREATE OR REPLACE FUNCTION {_functionName.QualifiedName}({argList}) RETURNS void LANGUAGE plpgsql AS $function$");
writer.WriteLine("BEGIN");
writer.WriteLine($"LOCK TABLE {_tableName.QualifiedName} IN SHARE ROW EXCLUSIVE MODE;");
writer.WriteLine($" WITH upsert AS (UPDATE {_tableName.QualifiedName} SET {updates} WHERE id=docId RETURNING *) ");
writer.WriteLine($" INSERT INTO {_tableName.QualifiedName} ({inserts})");
writer.WriteLine($" SELECT {valueList} WHERE NOT EXISTS (SELECT * FROM upsert);");
writer.WriteLine("END;");
writer.WriteLine("$function$;");
}
else
{
writer.WriteLine($"CREATE OR REPLACE FUNCTION {_functionName.QualifiedName}({argList}) RETURNS void LANGUAGE plpgsql AS $function$");
writer.WriteLine("BEGIN");
writer.WriteLine($"INSERT INTO {_tableName.QualifiedName} ({inserts}) VALUES ({valueList})");
writer.WriteLine($" ON CONFLICT ON CONSTRAINT {_primaryKeyConstraintName}");
writer.WriteLine($" DO UPDATE SET {updates};");
writer.WriteLine("END;");
writer.WriteLine("$function$;");
}
}
public void WriteFunctionSql(PostgresUpsertType upsertType, StringWriter writer)
{
var ordered = OrderedArguments();
var argList = ordered.Select(x => x.ArgumentDeclaration()).Join(", ");
var updates = ordered.Where(x => x.Column != "id")
.Select(x => $"\"{x.Column}\" = {x.Arg}").Join(", ");
var inserts = ordered.Select(x => $"\"{x.Column}\"").Join(", ");
var valueList = ordered.Select(x => x.Arg).Join(", ");
// CREATE OR REPLACE FUNCTION public.mt_upsert_user(arg_internal boolean, arg_user_name varchar, doc jsonb, docid uuid) RETURNS void LANGUAGE plpgsql AS $function$ BEGIN LOCK TABLE public.mt_doc_user IN SHARE ROW EXCLUSIVE MODE; WITH upsert AS (UPDATE public.mt_doc_user SET \"internal\" = arg_internal, \"user_name\" = arg_user_name, \"data\" = doc WHERE id=docId RETURNING *) INSERT INTO public.mt_doc_user (\"internal\", \"user_name\", \"data\", \"id\") SELECT arg_internal, arg_user_name, doc, docId WHERE NOT EXISTS (SELECT * FROM upsert); END; $function$
if (upsertType == PostgresUpsertType.Legacy)
{
writer.WriteLine($"CREATE OR REPLACE FUNCTION {_functionName.QualifiedName}({argList}) RETURNS void LANGUAGE plpgsql AS $function$");
writer.WriteLine("BEGIN");
writer.WriteLine($"LOCK TABLE {_tableName.QualifiedName} IN SHARE ROW EXCLUSIVE MODE;");
writer.WriteLine($" WITH upsert AS (UPDATE {_tableName.QualifiedName} SET {updates} WHERE id=docId RETURNING *) ");
writer.WriteLine($" INSERT INTO {_tableName.QualifiedName} ({inserts})");
writer.WriteLine($" SELECT {valueList} WHERE NOT EXISTS (SELECT * FROM upsert);");
writer.WriteLine("END;");
writer.WriteLine("$function$;");
}
else
{
writer.WriteLine($"CREATE OR REPLACE FUNCTION {_functionName.QualifiedName}({argList}) RETURNS void LANGUAGE plpgsql AS $function$");
writer.WriteLine("BEGIN");
writer.WriteLine($"INSERT INTO {_tableName.QualifiedName} ({inserts}) VALUES ({valueList})");
writer.WriteLine($" ON CONFLICT ON CONSTRAINT {_primaryKeyConstraintName}");
writer.WriteLine($" DO UPDATE SET {updates};");
writer.WriteLine("END;");
writer.WriteLine("$function$;");
}
}
