-more work on categories

13 files changed, 326 insertions, 150 deletions

diff --git a/src/backend/Makefile.am b/src/backend/Makefile.am
index 65ffa514..60ebee32 100644
--- a/src/backend/Makefile.am
+++ b/src/backend/Makefile.am
@@ -37,6 +37,8 @@ taler_merchant_httpd_SOURCES = \
     taler-merchant-httpd_mhd.h \
   taler-merchant-httpd_private-delete-account-ID.c \
     taler-merchant-httpd_private-delete-account-ID.h \
+  taler-merchant-httpd_private-delete-categories-ID.c \
+    taler-merchant-httpd_private-delete-categories-ID.h \
   taler-merchant-httpd_private-delete-instances-ID.c \
     taler-merchant-httpd_private-delete-instances-ID.h \
   taler-merchant-httpd_private-delete-instances-ID-token.c \
@@ -59,6 +61,10 @@ taler_merchant_httpd_SOURCES = \
     taler-merchant-httpd_private-get-accounts.h \
   taler-merchant-httpd_private-get-accounts-ID.c \
     taler-merchant-httpd_private-get-accounts-ID.h \
+  taler-merchant-httpd_private-get-categories.c \
+    taler-merchant-httpd_private-get-categories.h \
+  taler-merchant-httpd_private-get-categories-ID.c \
+    taler-merchant-httpd_private-get-categories-ID.h \
   taler-merchant-httpd_private-get-instances.c \
     taler-merchant-httpd_private-get-instances.h \
   taler-merchant-httpd_private-get-instances-ID.c \
@@ -95,6 +101,8 @@ taler_merchant_httpd_SOURCES = \
     taler-merchant-httpd_private-get-webhooks-ID.h \
   taler-merchant-httpd_private-patch-accounts-ID.c \
     taler-merchant-httpd_private-patch-accounts-ID.h \
+  taler-merchant-httpd_private-patch-categories-ID.c \
+    taler-merchant-httpd_private-patch-categories-ID.h \
   taler-merchant-httpd_private-patch-instances-ID.c \
     taler-merchant-httpd_private-patch-instances-ID.h \
   taler-merchant-httpd_private-patch-orders-ID-forget.c \
diff --git a/src/backend/taler-merchant-httpd_private-delete-categories-ID.c b/src/backend/taler-merchant-httpd_private-delete-categories-ID.c
index 47aab5a0..892dbd9c 100644
--- a/src/backend/taler-merchant-httpd_private-delete-categories-ID.c
+++ b/src/backend/taler-merchant-httpd_private-delete-categories-ID.c
@@ -46,7 +46,7 @@ TMH_private_delete_categories_ID (
   GNUNET_assert (NULL != mi);
   GNUNET_assert (NULL != hc->infix);
   if (1 != sscanf (hc->infix,
-                   "%llu%d",
+                   "%llu%c",
                    &cnum,
                    &dummy))
   {
diff --git a/src/backend/taler-merchant-httpd_private-get-categories-ID.c b/src/backend/taler-merchant-httpd_private-get-categories-ID.c
index bebe087f..02ef3495 100644
--- a/src/backend/taler-merchant-httpd_private-get-categories-ID.c
+++ b/src/backend/taler-merchant-httpd_private-get-categories-ID.c
@@ -38,35 +38,36 @@ TMH_private_get_categories_ID (
   struct TMH_HandlerContext *hc)
 {
   struct TMH_MerchantInstance *mi = hc->instance;
-  struct TALER_MERCHANTDB_OtpDeviceDetails tp = { 0 };
   enum GNUNET_DB_QueryStatus qs;
-  uint64_t faketime_s
-    = GNUNET_TIME_timestamp_to_s (GNUNET_TIME_timestamp_get ());
-  struct GNUNET_TIME_Timestamp my_time;
-  struct TALER_Amount price;
+  unsigned long long cnum;
+  char dummy;
+  struct TALER_MERCHANTDB_CategoryDetails cd;
 
-  TALER_MHD_parse_request_number (connection,
-                                  "faketime",
-                                  &faketime_s);
-  memset (&price,
-          0,
-          sizeof (price));
-  TALER_MHD_parse_request_amount (connection,
-                                  "price",
-                                  &price);
-  my_time = GNUNET_TIME_timestamp_from_s (faketime_s);
   GNUNET_assert (NULL != mi);
-  qs = TMH_db->select_otp (TMH_db->cls,
-                           mi->settings.id,
-                           hc->infix,
-                           &tp);
+  GNUNET_assert (NULL != hc->infix);
+  if (1 != sscanf (hc->infix,
+                   "%llu%c",
+                   &cnum,
+                   &dummy))
+  {
+    GNUNET_break_op (0);
+    return TALER_MHD_reply_with_error (connection,
+                                       MHD_HTTP_BAD_REQUEST,
+                                       TALER_EC_GENERIC_PARAMETER_MALFORMED,
+                                       "category_id must be a number");
+  }
+
+  qs = TMH_db->select_category (TMH_db->cls,
+                                mi->settings.id,
+                                cnum,
+                                &cd);
   if (0 > qs)
   {
     GNUNET_break (0);
     return TALER_MHD_reply_with_error (connection,
                                        MHD_HTTP_INTERNAL_SERVER_ERROR,
                                        TALER_EC_GENERIC_DB_FETCH_FAILED,
-                                       "select_otp");
+                                       "select_category");
   }
   if (GNUNET_DB_STATUS_SUCCESS_NO_RESULTS == qs)
   {
@@ -77,32 +78,39 @@ TMH_private_get_categories_ID (
   }
   {
     MHD_RESULT ret;
-    char *pos_confirmation;
+    json_t *products;
+
+    products = json_array ();
+    GNUNET_assert (NULL != products);
+    for (unsigned int i = 0; i<cd.num_products; i++)
+    {
+      const struct TALER_MERCHANTDB_ProductSummary *product
+        = &cd.products[i];
+      json_t *jprod;
+
+      jprod = GNUNET_JSON_PACK (
+        GNUNET_JSON_pack_string ("product_id",
+                                 product->product_id),
+        GNUNET_JSON_pack_string ("description",
+                                 product->description),
+        GNUNET_JSON_pack_object_steal ("description_i18n",
+                                       product->description_i18n));
+      GNUNET_assert (0 ==
+                     json_array_append_new (products,
+                                            jprod));
+    }
 
-    pos_confirmation = (NULL == tp.otp_key)
-      ? NULL
-      : TALER_build_pos_confirmation (tp.otp_key,
-                                      tp.otp_algorithm,
-                                      &price,
-                                      my_time);
-    /* Note: we deliberately (by design) do not return the otp_key */
     ret = TALER_MHD_REPLY_JSON_PACK (
       connection,
       MHD_HTTP_OK,
-      GNUNET_JSON_pack_string ("device_description",
-                               tp.otp_description),
+      GNUNET_JSON_pack_string ("name",
+                               cd.category_name),
       GNUNET_JSON_pack_allow_null (
-        GNUNET_JSON_pack_string ("otp_code",
-                                 pos_confirmation)),
-      GNUNET_JSON_pack_uint64 ("otp_timestamp",
-                               faketime_s),
-      GNUNET_JSON_pack_uint64 ("otp_algorithm",
-                               tp.otp_algorithm),
-      GNUNET_JSON_pack_uint64 ("otp_ctr",
-                               tp.otp_ctr));
-    GNUNET_free (pos_confirmation);
-    GNUNET_free (tp.otp_description);
-    GNUNET_free (tp.otp_key);
+        GNUNET_JSON_pack_object_incref ("name_i18n",
+                                        cd.category_name_i18n)),
+      GNUNET_JSON_pack_array_steal ("products",
+                                    products));
+    TALER_MERCHANTDB_category_details_free (&cd);
     return ret;
   }
 }
diff --git a/src/backend/taler-merchant-httpd_private-get-categories.c b/src/backend/taler-merchant-httpd_private-get-categories.c
index 74c0d6cd..8ebccb2b 100644
--- a/src/backend/taler-merchant-httpd_private-get-categories.c
+++ b/src/backend/taler-merchant-httpd_private-get-categories.c
@@ -23,27 +23,40 @@
 
 
 /**
- * Add OTP device details to our JSON array.
+ * Add category details to our JSON array.
  *
  * @param cls a `json_t *` JSON array to build
- * @param otp_id ID of the OTP device
- * @param otp_description human-readable description for the OTP device
+ * @param category_id ID of the category
+ * @param category_name name of the category
+ * @param category_name_i18n translations of the @a category_name
+ * @param product_count number of products in the category
  */
 static void
-add_otp (void *cls,
-         const char *otp_id,
-         const char *otp_description)
+add_category (void *cls,
+              uint64_t category_id,
+              const char *category_name,
+              const json_t *category_name_i18n,
+              uint64_t product_count)
 {
   json_t *pa = cls;
 
-  GNUNET_assert (0 ==
-                 json_array_append_new (
-                   pa,
-                   GNUNET_JSON_PACK (
-                     GNUNET_JSON_pack_string ("otp_device_id",
-                                              otp_id),
-                     GNUNET_JSON_pack_string ("device_description",
-                                              otp_description))));
+  GNUNET_assert (
+    0 ==
+    json_array_append_new (
+      pa,
+      GNUNET_JSON_PACK (
+        GNUNET_JSON_pack_uint64 (
+          "category_id",
+          category_id),
+        GNUNET_JSON_pack_string (
+          "name",
+          category_name),
+        GNUNET_JSON_pack_object_incref (
+          "name_i18n",
+          (json_t *) category_name_i18n),
+        GNUNET_JSON_pack_uint64 (
+          "product_count",
+          product_count))));
 }
 
 
@@ -59,7 +72,7 @@ TMH_private_get_categories (const struct TMH_RequestHandler *rh,
   GNUNET_assert (NULL != pa);
   qs = TMH_db->lookup_categories (TMH_db->cls,
                                   hc->instance->settings.id,
-                                  &add_otp,
+                                  &add_category,
                                   pa);
   if (0 > qs)
   {
diff --git a/src/backend/taler-merchant-httpd_private-patch-categories-ID.c b/src/backend/taler-merchant-httpd_private-patch-categories-ID.c
index 596b8b09..1aa489cf 100644
--- a/src/backend/taler-merchant-httpd_private-patch-categories-ID.c
+++ b/src/backend/taler-merchant-httpd_private-patch-categories-ID.c
@@ -1,6 +1,6 @@
 /*
   This file is part of TALER
-  (C) 2022 Taler Systems SA
+  (C) 2024 Taler Systems SA
 
   TALER is free software; you can redistribute it and/or modify
   it under the terms of the GNU Affero General Public License as
@@ -18,44 +18,49 @@
 */
 
 /**
- * @file taler-merchant-httpd_private-patch-otp-devices-ID.c
- * @brief implementing PATCH /otp-devices/$ID request handling
+ * @file taler-merchant-httpd_private-patch-categories-ID.c
+ * @brief implementing PATCH /categories/$ID request handling
  * @author Christian Grothoff
  */
 #include "platform.h"
-#include "taler-merchant-httpd_private-patch-otp-devices-ID.h"
+#include "taler-merchant-httpd_private-patch-categories-ID.h"
 #include "taler-merchant-httpd_helper.h"
 #include <taler/taler_json_lib.h>
 
 
 MHD_RESULT
-TMH_private_patch_otp_devices_ID (const struct TMH_RequestHandler *rh,
-                                  struct MHD_Connection *connection,
-                                  struct TMH_HandlerContext *hc)
+TMH_private_patch_categories_ID (const struct TMH_RequestHandler *rh,
+                                 struct MHD_Connection *connection,
+                                 struct TMH_HandlerContext *hc)
 {
   struct TMH_MerchantInstance *mi = hc->instance;
-  const char *device_id = hc->infix;
-  struct TALER_MERCHANTDB_OtpDeviceDetails tp = {0};
-  enum GNUNET_DB_QueryStatus qs;
+  unsigned long long cnum;
+  char dummy;
+  const char *category_name;
+  const json_t *category_name_i18n;
   struct GNUNET_JSON_Specification spec[] = {
-    GNUNET_JSON_spec_string ("otp_device_description",
-                             (const char **) &tp.otp_description),
-    TALER_JSON_spec_otp_type ("otp_algorithm",
-                              &tp.otp_algorithm),
-    GNUNET_JSON_spec_mark_optional (
-      GNUNET_JSON_spec_uint64 ("otp_ctr",
-                               &tp.otp_ctr),
-      NULL),
-    GNUNET_JSON_spec_mark_optional(
-
-      TALER_JSON_spec_otp_key ("otp_key",
-                               (const char **) &tp.otp_key),
-      NULL),
+    GNUNET_JSON_spec_string ("name",
+                             &category_name),
+    GNUNET_JSON_spec_object_const ("name_i18n",
+                                   &category_name_i18n),
     GNUNET_JSON_spec_end ()
   };
+  enum GNUNET_DB_QueryStatus qs;
 
   GNUNET_assert (NULL != mi);
-  GNUNET_assert (NULL != device_id);
+  GNUNET_assert (NULL != hc->infix);
+  if (1 != sscanf (hc->infix,
+                   "%llu%c",
+                   &cnum,
+                   &dummy))
+  {
+    GNUNET_break_op (0);
+    return TALER_MHD_reply_with_error (connection,
+                                       MHD_HTTP_BAD_REQUEST,
+                                       TALER_EC_GENERIC_PARAMETER_MALFORMED,
+                                       "category_id must be a number");
+  }
+
   {
     enum GNUNET_GenericReturnValue res;
 
@@ -68,10 +73,11 @@ TMH_private_patch_otp_devices_ID (const struct TMH_RequestHandler *rh,
              : MHD_NO;
   }
 
-  qs = TMH_db->update_otp (TMH_db->cls,
-                           mi->settings.id,
-                           device_id,
-                           &tp);
+  qs = TMH_db->update_category (TMH_db->cls,
+                                mi->settings.id,
+                                cnum,
+                                category_name,
+                                category_name_i18n);
   {
     MHD_RESULT ret = MHD_NO;
 
@@ -82,7 +88,7 @@ TMH_private_patch_otp_devices_ID (const struct TMH_RequestHandler *rh,
       ret = TALER_MHD_reply_with_error (connection,
                                         MHD_HTTP_INTERNAL_SERVER_ERROR,
                                         TALER_EC_GENERIC_DB_STORE_FAILED,
-                                        "update_pos");
+                                        "update_category");
       break;
     case GNUNET_DB_STATUS_SOFT_ERROR:
       GNUNET_break (0);
@@ -94,8 +100,8 @@ TMH_private_patch_otp_devices_ID (const struct TMH_RequestHandler *rh,
     case GNUNET_DB_STATUS_SUCCESS_NO_RESULTS:
       ret = TALER_MHD_reply_with_error (connection,
                                         MHD_HTTP_NOT_FOUND,
-                                        TALER_EC_MERCHANT_GENERIC_OTP_DEVICE_UNKNOWN,
-                                        device_id);
+                                        TALER_EC_MERCHANT_GENERIC_CATEGORY_UNKNOWN,
+                                        category_name);
       break;
     case GNUNET_DB_STATUS_SUCCESS_ONE_RESULT:
       ret = TALER_MHD_reply_static (connection,
@@ -111,4 +117,4 @@ TMH_private_patch_otp_devices_ID (const struct TMH_RequestHandler *rh,
 }
 
 
-/* end of taler-merchant-httpd_private-patch-otp-devices-ID.c */
+/* end of taler-merchant-httpd_private-patch-categories-ID.c */
diff --git a/src/backend/taler-merchant-httpd_private-post-categories.c b/src/backend/taler-merchant-httpd_private-post-categories.c
index 255fd2c4..9e708724 100644
--- a/src/backend/taler-merchant-httpd_private-post-categories.c
+++ b/src/backend/taler-merchant-httpd_private-post-categories.c
@@ -33,50 +33,23 @@
 #define MAX_RETRIES 3
 
 
-/**
- * Check if the two categories are identical.
- *
- * @param t1 device to compare
- * @param t2 other device to compare
- * @return true if they are 'equal', false if not or of payto_uris is not an array
- */
-static bool
-categories_equal (const struct TALER_MERCHANTDB_OtpDeviceDetails *t1,
-                  const struct TALER_MERCHANTDB_OtpDeviceDetails *t2)
-{
-  return ( (0 == strcmp (t1->otp_description,
-                         t2->otp_description)) &&
-           (0 == strcmp (t1->otp_key,
-                         t2->otp_key) ) &&
-           (t1->otp_ctr == t2->otp_ctr) &&
-           (t1->otp_algorithm == t2->otp_algorithm) );
-}
-
-
 MHD_RESULT
 TMH_private_post_categories (const struct TMH_RequestHandler *rh,
                              struct MHD_Connection *connection,
                              struct TMH_HandlerContext *hc)
 {
   struct TMH_MerchantInstance *mi = hc->instance;
-  struct TALER_MERCHANTDB_OtpDeviceDetails tp = { 0 };
-  const char *device_id;
-  enum GNUNET_DB_QueryStatus qs;
+  const char *category_name;
+  const json_t *category_name_i18n;
+  uint64_t category_id;
   struct GNUNET_JSON_Specification spec[] = {
-    GNUNET_JSON_spec_string ("otp_device_id",
-                             &device_id),
-    GNUNET_JSON_spec_string ("otp_device_description",
-                             (const char **) &tp.otp_description),
-    TALER_JSON_spec_otp_type ("otp_algorithm",
-                              &tp.otp_algorithm),
-    GNUNET_JSON_spec_mark_optional (
-      GNUNET_JSON_spec_uint64 ("otp_ctr",
-                               &tp.otp_ctr),
-      NULL),
-    TALER_JSON_spec_otp_key ("otp_key",
-                             (const char **) &tp.otp_key),
+    GNUNET_JSON_spec_string ("name",
+                             &category_name),
+    GNUNET_JSON_spec_object_const ("name_i18n",
+                                   &category_name_i18n),
     GNUNET_JSON_spec_end ()
   };
+  enum GNUNET_DB_QueryStatus qs;
 
   GNUNET_assert (NULL != mi);
   {
@@ -97,12 +70,11 @@ TMH_private_post_categories (const struct TMH_RequestHandler *rh,
   /* finally, interact with DB until no serialization error */
   for (unsigned int i = 0; i<MAX_RETRIES; i++)
   {
-    /* Test if a OTP device of this id is known */
-    struct TALER_MERCHANTDB_OtpDeviceDetails etp;
+    json_t *xcategory_name_i18n;
 
     if (GNUNET_OK !=
         TMH_db->start (TMH_db->cls,
-                       "/post categories"))
+                       "POST /categories"))
     {
       GNUNET_break (0);
       GNUNET_JSON_parse_free (spec);
@@ -111,10 +83,11 @@ TMH_private_post_categories (const struct TMH_RequestHandler *rh,
                                          TALER_EC_GENERIC_DB_START_FAILED,
                                          NULL);
     }
-    qs = TMH_db->select_otp (TMH_db->cls,
-                             mi->settings.id,
-                             device_id,
-                             &etp);
+    qs = TMH_db->select_category_by_name (TMH_db->cls,
+                                          mi->settings.id,
+                                          category_name,
+                                          &xcategory_name_i18n,
+                                          &category_id);
     switch (qs)
     {
     case GNUNET_DB_STATUS_HARD_ERROR:
@@ -137,29 +110,30 @@ TMH_private_post_categories (const struct TMH_RequestHandler *rh,
       {
         bool eq;
 
-        eq = categories_equal (&tp,
-                               &etp);
-        GNUNET_free (etp.otp_description);
-        GNUNET_free (etp.otp_key);
+        eq = ( (0 == strcmp (cd.category_name,
+                             category_name)) &&
+               (1 == json_equal (xcategory_name_i18n,
+                                 category_name_i18n)) );
+        json_decref (xcategory_name_i18n);
         TMH_db->rollback (TMH_db->cls);
         GNUNET_JSON_parse_free (spec);
         return eq
-          ? TALER_MHD_reply_static (connection,
-                                    MHD_HTTP_NO_CONTENT,
-                                    NULL,
-                                    NULL,
-                                    0)
+          ? TALER_MHD_REPLY_JSON_PACK (connection,
+                                       MHD_HTTP_OK,
+                                       GNUNET_JSON_pack_uint64 ("category_id",
+                                                                category_id))
           : TALER_MHD_reply_with_error (connection,
                                         MHD_HTTP_CONFLICT,
                                         TALER_EC_MERCHANT_PRIVATE_POST_CATEGORIES_CONFLICT_CATEGORY_EXISTS,
-                                        device_id);
+                                        category_name);
       }
     } /* end switch (qs) */
 
-    qs = TMH_db->insert_otp (TMH_db->cls,
-                             mi->settings.id,
-                             device_id,
-                             &tp);
+    qs = TMH_db->insert_category (TMH_db->cls,
+                                  mi->settings.id,
+                                  category_name,
+                                  category_name_i18n,
+                                  &category_id);
     if (GNUNET_DB_STATUS_HARD_ERROR == qs)
     {
       TMH_db->rollback (TMH_db->cls);
@@ -187,11 +161,11 @@ retry:
       : TALER_EC_GENERIC_DB_COMMIT_FAILED,
       NULL);
   }
-  return TALER_MHD_reply_static (connection,
-                                 MHD_HTTP_NO_CONTENT,
-                                 NULL,
-                                 NULL,
-                                 0);
+  return TALER_MHD_REPLY_JSON_PACK (
+    connection,
+    MHD_HTTP_OK,
+    GNUNET_JSON_pack_uint64 ("category_id",
+                             category_id));
 }
 
 
diff --git a/src/backenddb/Makefile.am b/src/backenddb/Makefile.am
index 8d1c582c..99ef1e77 100644
--- a/src/backenddb/Makefile.am
+++ b/src/backenddb/Makefile.am
@@ -78,6 +78,7 @@ libtaler_plugin_merchantdb_postgres_la_SOURCES = \
   pg_store_wire_fee_by_exchange.h pg_store_wire_fee_by_exchange.c \
   pg_select_open_transfers.h pg_select_open_transfers.c \
   pg_lookup_categories.h pg_lookup_categories.c \
+  pg_select_category_by_name.h pg_select_category_by_name.c \
   pg_select_category.h pg_select_category.c \
   pg_update_category.h pg_update_category.c \
   pg_insert_category.h pg_insert_category.c \
diff --git a/src/backenddb/merchantdb_helper.c b/src/backenddb/merchantdb_helper.c
index 5894525c..6c1d2ec6 100644
--- a/src/backenddb/merchantdb_helper.c
+++ b/src/backenddb/merchantdb_helper.c
@@ -83,4 +83,25 @@ TALER_MERCHANTDB_token_family_details_free (
 }
 
 
+void
+TALER_MERCHANTDB_category_details_free (
+  struct TALER_MERCHANTDB_CategoryDetails *cd)
+{
+  GNUNET_free (cd->category_name);
+  json_decref (cd->category_name_i18n);
+  for (unsigned int i = 0; i<cd->num_products; i++)
+  {
+    struct TALER_MERCHANTDB_ProductSummary *ps
+      = &cd->products[i];
+
+    GNUNET_free (ps->product_id);
+    GNUNET_free (ps->description);
+    json_decref (ps->description_i18n);
+  }
+  GNUNET_array_grow (cd->products,
+                     cd->num_products,
+                     0);
+}
+
+
 /* end of merchantdb_helper.c */
diff --git a/src/backenddb/pg_select_category_by_name.c b/src/backenddb/pg_select_category_by_name.c
new file mode 100644
index 00000000..9c2aa784
--- /dev/null
+++ b/src/backenddb/pg_select_category_by_name.c
@@ -0,0 +1,68 @@
+/*
+   This file is part of TALER
+   Copyright (C) 2024 Taler Systems SA
+
+   TALER is free software; you can redistribute it and/or modify it under the
+   terms of the GNU General Public License as published by the Free Software
+   Foundation; either version 3, or (at your option) any later version.
+
+   TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+   WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
+   A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License along with
+   TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
+ */
+/**
+ * @file backenddb/pg_select_category_by_name.c
+ * @brief Implementation of the select_category_by_name function for Postgres
+ * @author Christian Grothoff
+ */
+#include "platform.h"
+#include <taler/taler_error_codes.h>
+#include <taler/taler_dbevents.h>
+#include <taler/taler_pq_lib.h>
+#include "pg_select_category_by_name.h"
+#include "pg_helper.h"
+
+
+enum GNUNET_DB_QueryStatus
+TMH_PG_select_category_by_name (void *cls,
+                                const char *instance_id,
+                                const char *category_name,
+                                json_t **name_i18n,
+                                uint64_t *category_id)
+{
+  struct PostgresClosure *pg = cls;
+  struct GNUNET_PQ_QueryParam params[] = {
+    GNUNET_PQ_query_param_string (instance_id),
+    GNUNET_PQ_query_param_string (category_name),
+    GNUNET_PQ_query_param_end
+  };
+  struct GNUNET_PQ_ResultSpec rs[] = {
+    GNUNET_PQ_result_spec_uint64 ("category_serial",
+                                  category_id),
+    TALER_PQ_result_spec_json ("category_name_i18n",
+                               name_i18n),
+    GNUNET_PQ_result_spec_end
+  };
+
+  PREPARE (pg,
+           "select_category_by_name",
+           "SELECT"
+           " category_serial"
+           ",category_name_i18n"
+           " FROM merchant_categories mc"
+           " JOIN merchant_instances mi"
+           "   USING (merchant_serial)"
+           " WHERE mi.merchant_id=$1"
+           "   AND mc.category_name=$2");
+
+
+  check_connection (pg);
+  return GNUNET_PQ_eval_prepared_singleton_select (
+    pg->conn,
+    "select_category_by_name",
+    params,
+    rs);
+}
diff --git a/src/backenddb/pg_select_category_by_name.h b/src/backenddb/pg_select_category_by_name.h
new file mode 100644
index 00000000..4f582828
--- /dev/null
+++ b/src/backenddb/pg_select_category_by_name.h
@@ -0,0 +1,46 @@
+/*
+   This file is part of TALER
+   Copyright (C) 2024 Taler Systems SA
+
+   TALER is free software; you can redistribute it and/or modify it under the
+   terms of the GNU General Public License as published by the Free Software
+   Foundation; either version 3, or (at your option) any later version.
+
+   TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+   WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
+   A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
+
+   You should have received a copy of the GNU General Public License along with
+   TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
+ */
+/**
+ * @file backenddb/pg_select_category_by_name.h
+ * @brief implementation of the select_category_by_name function for Postgres
+ * @author Christian Grothoff
+ */
+#ifndef PG_SELECT_CATEGORY_BY_NAME_H
+#define PG_SELECT_CATEGORY_BY_NAME_H
+
+#include <taler/taler_util.h>
+#include <taler/taler_json_lib.h>
+#include "taler_merchantdb_plugin.h"
+
+
+/**
+ * Lookup details about product category by name.
+ *
+ * @param cls closure
+ * @param instance_id instance to lookup template for
+ * @param category_name category name to look for
+ * @param[out] name_i18n category name translation
+ * @param[out] category_id category ID
+ * @return database result code
+ */
+enum GNUNET_DB_QueryStatus
+TMH_PG_select_category_by_name (void *cls,
+                                const char *instance_id,
+                                const char *category_name,
+                                json_t **name_i18n,
+                                uint64_t *category_id);
+
+#endif
diff --git a/src/backenddb/plugin_merchantdb_postgres.c b/src/backenddb/plugin_merchantdb_postgres.c
index ede43100..6c5c7a5b 100644
--- a/src/backenddb/plugin_merchantdb_postgres.c
+++ b/src/backenddb/plugin_merchantdb_postgres.c
@@ -62,6 +62,7 @@
 #include "pg_account_kyc_set_status.h"
 #include "pg_account_kyc_get_status.h"
 #include "pg_delete_instance_private_key.h"
+#include "pg_select_category_by_name.h"
 #include "pg_purge_instance.h"
 #include "pg_update_instance.h"
 #include "pg_update_instance_auth.h"
@@ -571,6 +572,8 @@ libtaler_plugin_merchantdb_postgres_init (void *cls)
     = &TMH_PG_update_pending_webhook;
   plugin->lookup_categories
     = &TMH_PG_lookup_categories;
+  plugin->select_category_by_name
+    = &TMH_PG_select_category_by_name;
   plugin->select_category
     = &TMH_PG_select_category;
   plugin->update_category
diff --git a/src/include/taler_merchantdb_lib.h b/src/include/taler_merchantdb_lib.h
index 3a641a54..a78d01f1 100644
--- a/src/include/taler_merchantdb_lib.h
+++ b/src/include/taler_merchantdb_lib.h
@@ -97,6 +97,16 @@ void
 TALER_MERCHANTDB_token_family_details_free (
   struct TALER_MERCHANTDB_TokenFamilyDetails *tf);
 
+
+/**
+ * Free members of @a cd, but not @a cd itself.
+ *
+ * @param[in] cd token family details to clean up
+ */
+void
+TALER_MERCHANTDB_category_details_free (
+  struct TALER_MERCHANTDB_CategoryDetails *cd);
+
 #endif  /* MERCHANT_DB_H */
 
 /* end of taler_merchantdb_lib.h */
diff --git a/src/include/taler_merchantdb_plugin.h b/src/include/taler_merchantdb_plugin.h
index d06ab76c..7c009b89 100644
--- a/src/include/taler_merchantdb_plugin.h
+++ b/src/include/taler_merchantdb_plugin.h
@@ -3141,6 +3141,24 @@ struct TALER_MERCHANTDB_Plugin
 
 
   /**
+   * Lookup details about product category by name.
+   *
+   * @param cls closure
+   * @param instance_id instance to lookup template for
+   * @param category_name category name to look for
+   * @param[out] name_i18n category name translation
+   * @param[out] category_id category ID
+   * @return database result code
+   */
+  enum GNUNET_DB_QueryStatus
+  (*select_category_by_name)(void *cls,
+                             const char *instance_id,
+                             const char *category_name,
+                             json_t **name_i18n,
+                             uint64_t *category_id);
+
+
+  /**
    * Lookup all of the webhooks the given instance has configured.
    *
    * @param cls closure