X-Git-Url: http://git.squeep.com/?p=squeep-indie-auther;a=blobdiff_plain;f=src%2Fdb%2Fpostgres%2Fsql%2Fscope-upsert.sql;fp=src%2Fdb%2Fpostgres%2Fsql%2Fscope-upsert.sql;h=543d89da71b638792de2cf4533831e995bc0b879;hp=0000000000000000000000000000000000000000;hb=b0103b0d496262c438b40bc20304081dbfe41e73;hpb=8ed81748bce7cea7904cac7225b20a60cafdfc16

diff --git a/src/db/postgres/sql/scope-upsert.sql b/src/db/postgres/sql/scope-upsert.sql
new file mode 100644
index 0000000..543d89d
--- /dev/null
+++ b/src/db/postgres/sql/scope-upsert.sql
@@ -0,0 +1,14 @@
+--
+-- N.B. weirdness with postgres empty string null equivalence and coalesce
+INSERT INTO scope (
+	scope, application, description, is_manually_added
+) VALUES (
+	$(scope),
+	CASE WHEN $(application) IS NULL THEN '' ELSE $(application) END,
+	CASE WHEN $(description) IS NULL THEN '' ELSE $(description) END,
+	COALESCE($(manuallyAdded), false)
+) ON CONFLICT (scope) DO UPDATE
+SET
+	application = CASE WHEN $(application) IS NULL THEN EXCLUDED.application ELSE $(application) END,
+	description = CASE WHEN $(description) IS NULL THEN EXCLUDED.description ELSE $(description) END,
+	is_manually_added = EXCLUDED.is_manually_added OR COALESCE($(manuallyAdded), false)