steve-chavez · June 22, 2022 17:40 · Aug 6, 2014 · Aug 6, 2014 · Aug 6, 2014 · Aug 6, 2014
diff --git a/spi.cc b/spi.cc
@@ -46,7 +46,7 @@ Datum have_ids (PG_FUNCTION_ARGS) try {
       //std::cout << "have_ids] tok " << tok << std::endl;  // WJB_BEGIN_ARRAY, WJB_ELEM, WJB_END_ARRAY.
       if (tok == WJB_ELEM) {
         if (val.type != JsonbValue::jbvNumeric) GTHROW ("!jbvNumeric: " + std::to_string (val.type));
-		    int32_t id = numericToInt32 (val.val.numeric);
+        int32_t id = numericToInt32 (val.val.numeric);
         //std::cout << "have_ids] " << id << std::endl;
         GSTRING_ON_STACK (sql, 256) << "SELECT COUNT(*) FROM " << table << " WHERE id = " << id;
         //std::cout << "have_ids] " << sql << std::endl;

diff --git a/spi.cc b/spi.cc
@@ -36,7 +36,7 @@ PG_FUNCTION_INFO_V1 (have_ids);  // http://www.postgresql.org/docs/9.4/interacti
 /// Checks if all the ids from the given array are present in the given table.
 /// SELECT have_ids ('foo', '[1, 2, 3]');
 Datum have_ids (PG_FUNCTION_ARGS) try {
-  bool missingCategories {false};
+  bool missingIds {false};
   const char* table = PG_GETARG_CSTRING (0);
   Jsonb* categories = PG_GETARG_JSONB (1);
   JsonbIterator* it = JsonbIteratorInit (&categories->root); if (it) {
@@ -56,12 +56,12 @@ Datum have_ids (PG_FUNCTION_ARGS) try {
         bool isNull {false};
         Datum count = SPI_getbinval (SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isNull);
         if (isNull) GTHROW ("isNull");
-        if (DatumGetInt32 (count) != 1) {missingCategories = true; break;}
+        if (DatumGetInt32 (count) != 1) {missingIds = true; break;}
       }
     }
     if (SPI_finish() != SPI_OK_FINISH) GTHROW ("!SPI_finish");
   }
-  PG_RETURN_BOOL (!missingCategories);
+  PG_RETURN_BOOL (!missingIds);
 } catch (const std::exception& ex) {
   GSTRING_ON_STACK (error, 256) << "have_ids] " << ex.what();
   elog (ERROR, error.c_str());

diff --git a/spi.cc b/spi.cc
@@ -27,11 +27,11 @@ void _PG_init() {
 
 PG_FUNCTION_INFO_V1 (have_ids);  // http://www.postgresql.org/docs/9.4/interactive/xfunc-c.html
 
-// Register with (su - postgres; psql byzon):
+// Register with (su - postgres; psql db):
 // CREATE FUNCTION have_ids (cstring, jsonb) RETURNS BOOLEAN AS '/var/lib/postgresql/spi', 'have_ids' LANGUAGE C STABLE STRICT COST 100;
 // cf. http://www.postgresql.org/docs/9.4/interactive/sql-createfunction.html
 // Check with:
-// echo "SELECT have_ids ('foo', '[1, 2, 3]');" | sudo -u postgres psql byzon
+// echo "SELECT have_ids ('foo', '[1, 2, 3]');" | sudo -u postgres psql db
 
 /// Checks if all the ids from the given array are present in the given table.
 /// SELECT have_ids ('foo', '[1, 2, 3]');

diff --git a/spi.cc b/spi.cc
@@ -31,11 +31,10 @@ PG_FUNCTION_INFO_V1 (have_ids);  // http://www.postgresql.org/docs/9.4/interacti
 // CREATE FUNCTION have_ids (cstring, jsonb) RETURNS BOOLEAN AS '/var/lib/postgresql/spi', 'have_ids' LANGUAGE C STABLE STRICT COST 100;
 // cf. http://www.postgresql.org/docs/9.4/interactive/sql-createfunction.html
 // Check with:
-// echo "SELECT have_ids ('shop_categories', '[1, 2, 3]');" | sudo -u postgres psql byzon
+// echo "SELECT have_ids ('foo', '[1, 2, 3]');" | sudo -u postgres psql byzon
 
 /// Checks if all the ids from the given array are present in the given table.
-/// SELECT have_ids ('shop_categories', '[1, 2, 3]');
-/// The function is used to CHECK the shop_sttributes table (#75759804).
+/// SELECT have_ids ('foo', '[1, 2, 3]');
 Datum have_ids (PG_FUNCTION_ARGS) try {
   bool missingCategories {false};
   const char* table = PG_GETARG_CSTRING (0);

diff --git a/makefile b/makefile
@@ -1,4 +1,3 @@
-
 all: spi.so
 
 spi.so: spi.o makefile

diff --git a/spi.cc b/spi.cc
@@ -1,5 +1,5 @@
 #include <iostream>
-#include <glim/gstring.hpp>
+#include <glim/gstring.hpp>  // https://code.google.com/p/libglim/
 
 extern "C" {
 #include <postgres.h>

diff --git a/makefile b/makefile
@@ -0,0 +1,12 @@
+
+all: spi.so
+
+spi.so: spi.o makefile
+	g++ -shared -o spi.so spi.o
+	cp --remove-destination spi.so /var/lib/postgresql/spi.so
+
+spi.o: spi.cc makefile
+	g++ -g -O2 -Wall -std=c++11 -fpic -c -o spi.o -I/usr/include/postgresql -I/usr/include/postgresql/9.4/server spi.cc
+
+clean:
+	rm -f spi.o spi.so
diff --git a/spi.cc b/spi.cc
@@ -0,0 +1,75 @@
+#include <iostream>
+#include <glim/gstring.hpp>
+
+extern "C" {
+#include <postgres.h>
+#include <executor/spi.h>  // postgresql-server-dev-9.4
+#include <utils/builtins.h>
+#include <utils/jsonb.h>
+}
+
+static inline int32_t numericToInt32 (Numeric numeric) {
+  return DatumGetInt32 (DirectFunctionCall1 (numeric_int4, NumericGetDatum (numeric)));
+}
+
+// http://www.postgresql.org/docs/9.4/interactive/xfunc-c.html
+// http://www.postgresql.org/docs/9.4/interactive/spi.html
+
+extern "C" {
+
+#ifdef PG_MODULE_MAGIC
+PG_MODULE_MAGIC;
+#endif
+
+void _PG_init() {
+  //std::cout << "have_ids spi init." << std::endl;  // Goes into /var/log/postgresql/postgresql-9.4-main.log
+}
+
+PG_FUNCTION_INFO_V1 (have_ids);  // http://www.postgresql.org/docs/9.4/interactive/xfunc-c.html
+
+// Register with (su - postgres; psql byzon):
+// CREATE FUNCTION have_ids (cstring, jsonb) RETURNS BOOLEAN AS '/var/lib/postgresql/spi', 'have_ids' LANGUAGE C STABLE STRICT COST 100;
+// cf. http://www.postgresql.org/docs/9.4/interactive/sql-createfunction.html
+// Check with:
+// echo "SELECT have_ids ('shop_categories', '[1, 2, 3]');" | sudo -u postgres psql byzon
+
+/// Checks if all the ids from the given array are present in the given table.
+/// SELECT have_ids ('shop_categories', '[1, 2, 3]');
+/// The function is used to CHECK the shop_sttributes table (#75759804).
+Datum have_ids (PG_FUNCTION_ARGS) try {
+  bool missingCategories {false};
+  const char* table = PG_GETARG_CSTRING (0);
+  Jsonb* categories = PG_GETARG_JSONB (1);
+  JsonbIterator* it = JsonbIteratorInit (&categories->root); if (it) {
+    if (SPI_connect() != SPI_OK_CONNECT) GTHROW ("!SPI_connect");
+    JsonbValue val; JsonbIteratorToken tok;
+    while ((tok = JsonbIteratorNext (&it, &val, true)) != WJB_DONE) {
+      //std::cout << "have_ids] tok " << tok << std::endl;  // WJB_BEGIN_ARRAY, WJB_ELEM, WJB_END_ARRAY.
+      if (tok == WJB_ELEM) {
+        if (val.type != JsonbValue::jbvNumeric) GTHROW ("!jbvNumeric: " + std::to_string (val.type));
+		    int32_t id = numericToInt32 (val.val.numeric);
+        //std::cout << "have_ids] " << id << std::endl;
+        GSTRING_ON_STACK (sql, 256) << "SELECT COUNT(*) FROM " << table << " WHERE id = " << id;
+        //std::cout << "have_ids] " << sql << std::endl;
+        if (SPI_execute (sql.c_str(), true, 1) != SPI_OK_SELECT) GTHROW ("!SPI_execute");
+        if (SPI_processed != 1) GTHROW ("!SPI_processed");  // Number of rows processed.
+        if (SPI_tuptable == nullptr) GTHROW ("!SPI_tuptable");
+        bool isNull {false};
+        Datum count = SPI_getbinval (SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isNull);
+        if (isNull) GTHROW ("isNull");
+        if (DatumGetInt32 (count) != 1) {missingCategories = true; break;}
+      }
+    }
+    if (SPI_finish() != SPI_OK_FINISH) GTHROW ("!SPI_finish");
+  }
+  PG_RETURN_BOOL (!missingCategories);
+} catch (const std::exception& ex) {
+  GSTRING_ON_STACK (error, 256) << "have_ids] " << ex.what();
+  elog (ERROR, error.c_str());
+}
+
+void _PG_fini() {
+  //std::cout << "have_ids spi fini." << std::endl;
+}
+
+}