From ad2a2ff05af8df1ba552d906e377afa345a449a7 Mon Sep 17 00:00:00 2001
From: Dan Goodliffe <dan@randomdan.homeip.net>
Date: Sun, 28 Jun 2026 18:47:14 +0100
Subject: Fix entity function sometimes returning null/wrong id

Includes tests over the errant behaviour.
Didn't match on type and control flow could return multiple rows.
---
 src/schema.sql | 24 ++++++++++++------------
 1 file changed, 12 insertions(+), 12 deletions(-)

(limited to 'src/schema.sql')

diff --git a/src/schema.sql b/src/schema.sql
index 81080ff..6f28f2d 100644
--- a/src/schema.sql
+++ b/src/schema.sql
@@ -57,11 +57,6 @@ DECLARE
 	recid integer;
 	nulldetail boolean;
 BEGIN
-	IF newValue IS NULL THEN
-		RETURN query
-	VALUES (NULL,
-		NULL);
-	END IF;
 	INSERT INTO entities(value, type)
 	SELECT
 		newValue,
@@ -72,26 +67,31 @@ BEGIN
 			FROM
 				entities
 			WHERE
-				md5(value) = md5(newValue))
+				md5(value) = md5(newValue)
+				AND type = newType)
 	ON CONFLICT
 		DO NOTHING
 	RETURNING
 		entities.id,
-		entities.detail IS NULL INTO recid,
+		entities.detail IS NULL
+	INTO
+		recid,
 		nulldetail;
 	IF recid IS NULL THEN
-		RETURN query
+		RETURN QUERY
 		SELECT
 			e.id,
 			e.detail IS NULL
 		FROM
 			entities e
 		WHERE
-			md5(e.value) = md5(newValue);
+			md5(e.value) = md5(newValue)
+			AND e.type = newType;
+	ELSE
+		RETURN QUERY
+	VALUES (recid,
+		nulldetail);
 	END IF;
-	RETURN query
-VALUES (recid,
-	nulldetail);
 END;
 $$
 LANGUAGE plpgSQL
-- 
cgit v1.3