-
-
Save steve-chavez/7e84d85a80c23554a0ef639fdf69e5db to your computer and use it in GitHub Desktop.
SPI helper for a PostgreSQL CHECK when using one-to-many with a jsonb.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #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; | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Motivation on https://stackoverflow.com/questions/24489647/json-foreign-keys-in-postgresql