initial commit

1 files changed, 25 insertions, 0 deletions

diff --git a/sql/functions/auth.get_registration_by_hash.sql b/sql/functions/auth.get_registration_by_hash.sql
new file mode 100644
index 0000000..87d622e
--- /dev/null
+++ b/sql/functions/auth.get_registration_by_hash.sql
@@ -0,0 +1,25 @@
+drop function auth.get_registration_by_hash (
+    p_hash text
+);
+
+create or replace function auth.get_registration_by_hash (
+    p_hash text
+)
+returns setof auth.registrations_t
+as $$
+begin
+    return query
+    select r.id,
+        substring(r.hash::text from 3),
+        r.first_name,
+        r.last_name,
+        r.email,
+        r.role_groups,
+        r.created,
+        (r.created  - (now() - interval '3 days'))::interval as valid_for
+    from auth.registrations r
+    where r.hash = ('\x' || p_hash)::bytea;
+
+    return;
+end;
+$$ language plpgsql;