--- title: PostgreSQL description: Install the extension in PostgreSQL 18 and ask a question about every message a query returns, with thinkthen.decide and nine more set-returning functions. goal: The reader installs the PostgreSQL extension, asks a first question of a table, sees not sure and a failure as psql prints them, lets other roles call it, and finds the query for each of the ten functions. reader: A database administrator or developer who wants answers as columns in PostgreSQL kind: reference verified: 2026-11-03 --- # PostgreSQL The PostgreSQL extension adds the ten functions in the `thinkthen` schema; each one runs a query you pass as text, asks a question about every message it returns, and gives the answer as a column, such as `t` or `f` from decide. ```sql CREATE SCHEMA thinkthen; CREATE EXTENSION thinkthen SCHEMA thinkthen; CREATE TABLE messages ( msg_id integer PRIMARY KEY, body text); INSERT INTO messages VALUES (1, 'Please refund my order.'), (2, 'Where is my parcel?'), (3, 'I was charged twice for one invoice.'), (4, 'I was charged twice and my parcel is late again.'), (5, 'Morgan works for Example Group. Please refund my order.'), (6, 'Morgan works for Example Group.'); SELECT id AS msg_id, answer AS is_refund, status FROM thinkthen.decide( 'SELECT msg_id AS id, body AS record FROM messages WHERE msg_id <= 3', 'Does the customer request a refund?'); ``` Output ```text CREATE SCHEMA CREATE EXTENSION CREATE TABLE INSERT 0 6 msg_id | is_refund | status --------+-----------+---------- 1 | t | answered 2 | f | answered 3 | t | answered (3 rows) ``` Messages 1 and 3 ask for a refund, so `is_refund` is `t`, and message 2 asks about a parcel, so it is `f`. The rest of this page uses the same database and the same `messages` table. ## Install the extension and ask The extension runs on PostgreSQL 18, on Linux x86-64 or arm64 with glibc 2.28 or later. Download the PostgreSQL 18 archive for your server from the [0.2.0 release](https://github.com/botassembly/thinkthen/releases/tag/v0.2.0), check it as [Installation](/install/) shows, and copy its files into the server's folders: ```sh mkdir thinkthen-postgres tar -xzf thinkthen-postgres-*.tar.gz -C thinkthen-postgres cd thinkthen-postgres sudo cp lib/thinkthen_postgres.so \ "$(pg_config --pkglibdir)/" sudo cp share/extension/* \ "$(pg_config --sharedir)/extension/" ``` Set your key in the server's environment, as [Configuration](/configuration/) shows, and restart the server. SQL can never set the key. Connect with `psql` as an administrator. The first argument is the query to judge, as text. Its `record` column holds the text, and its `id` column comes back in `id` as `jsonb`, so `(id)::integer` joins it back to your table. Name the other arguments with `=>`, as `choices => ARRAY[...]`. PostgreSQL runs the whole function before it applies an outer `LIMIT`, so put any `LIMIT` inside the query you pass. Each run of a query asks again and is billed again, so save answers you need twice with `CREATE TABLE refund_answers AS SELECT ...` and query that table. To move from a 0.1 test build, back up first, then drop and recreate the extension as the [PostgreSQL guide](https://github.com/botassembly/thinkthen/blob/v0.2.0/docs/surfaces/postgresql.md#move-from-a-01-build) shows. ## When decide is not sure Set a low bar and a high bar with `low` and `high`. decide says no below the low bar, yes at or above the high bar, and not sure in between: ```sql SELECT id AS msg_id, answer AS is_refund, status FROM thinkthen.decide( 'SELECT msg_id AS id, body AS record FROM messages WHERE msg_id <= 3', 'Does the customer request a refund?', low => 0.2, high => 0.8); ``` Output ```text msg_id | is_refund | status --------+-----------+---------- 1 | t | answered 2 | f | answered 3 | | unsure (3 rows) ``` A double charge might be a refund request or might not, so message 3 comes back `unsure`, with a NULL answer, which psql prints as a blank. Send it to a person. Test answers with `IS TRUE` and `IS FALSE`, which are both false for NULL, so not sure never counts as a no. ## When a message fails A message that can't be judged comes back with `status` `failed`, a NULL answer and an `error_code`, and the query keeps going. Here message 7 has no text, so it fails as `record.missing`, and no question is sent for it: ```sql SELECT id AS msg_id, answer AS is_refund, status, error_code FROM thinkthen.decide( 'SELECT msg_id AS id, body AS record FROM messages WHERE msg_id = 1 UNION ALL SELECT 7, NULL', 'Does the customer request a refund?'); ``` Output ```text msg_id | is_refund | status | error_code --------+-----------+----------+---------------- 1 | t | answered | 7 | | failed | record.missing (2 rows) ``` A mistake in the call itself, such as leaving out `choices`, raises an error before anything is sent. Its `DETAIL` holds the code, such as `usage.question`, and many carry a `HINT` with the fix. When every message fails for the same outside reason, such as a refused request, the query raises that error instead. [Troubleshooting](/reference/troubleshooting/) lists every code. ## The ten functions Each query uses the `messages` table from the first example. Open a function to see its query and what psql prints. In psql, `\df+ thinkthen.*` describes each function with an example. ### decide
Yes or no for each message ```sql SELECT id AS msg_id, answer AS is_refund, probability FROM thinkthen.decide( 'SELECT msg_id AS id, body AS record FROM messages WHERE msg_id <= 3', 'Does the customer request a refund?'); ``` Output ```text msg_id | is_refund | probability --------+-----------+------------- 1 | t | 0.99 2 | f | 0.03 3 | t | 0.7 (3 rows) ``` `answer` is a `boolean`. `probability` is the chance of yes, also when the answer is no. It is high for message 1, low for message 2, and in between for message 3, the double charge. `yes_means` and `no_means` say what yes and no mean for your question. [decide](/functions/decide/) shows the command.
### choose
One of your choices for each message ```sql SELECT id AS msg_id, answer AS topic, status FROM thinkthen.choose( 'SELECT msg_id AS id, body AS record FROM messages WHERE msg_id = 1', 'What is the message about?', choices => ARRAY['billing', 'shipping', 'other']); ``` Output ```text msg_id | topic | status --------+---------+---------- 1 | billing | answered (1 row) ``` Message 1, a refund request, is about billing. `answer` is the choice, as `text`, and `detail -> 'labels'` gives every choice its probability. With `threshold`, or `low` and `high`, choose is not sure when the best choice falls short of the bar. [choose](/functions/choose/) shows the command.
### tag
Every label that fits each message ```sql SELECT id AS msg_id, answer AS topics, status FROM thinkthen.tag( 'SELECT msg_id AS id, body AS record FROM messages WHERE msg_id = 4', 'Which topics appear?', labels => ARRAY['billing', 'shipping']); ``` Output ```text msg_id | topics | status --------+--------------------+---------- 4 | {billing,shipping} | answered (1 row) ``` Message 4 is about billing and shipping at once. `answer` is a `text[]`, which is empty when no label fits. `threshold` sets the bar a label must reach. [tag](/functions/tag/) shows the command.
### score
A number from 0 to 1 for each message ```sql SELECT id AS msg_id, answer AS urgency, status FROM thinkthen.score( 'SELECT msg_id AS id, body AS record FROM messages WHERE msg_id <= 3', 'How urgent is the request?', levels => ARRAY['routine', 'urgent']); ``` Output ```text msg_id | urgency | status --------+---------+---------- 1 | 0.05 | answered 2 | 0.14 | answered 3 | 0.45 | answered (3 rows) ``` The double charge, message 3, is the most urgent of the three. `answer` is a `float8`: 0 means the first level, and 1 the last. List the levels lowest first. [score](/functions/score/) shows the command.
### filter
Only the messages that answer yes ```sql SELECT id AS msg_id, status FROM thinkthen.filter( 'SELECT msg_id AS id, body AS record FROM messages WHERE msg_id <= 3', 'Does the customer request a refund?'); ``` Output ```text msg_id | status --------+---------- 1 | answered 3 | answered (2 rows) ``` Messages 1 and 3 ask for a refund, so filter keeps them. The no and not-sure messages are left out. If a message fails, the query judges them all, then raises one error that says how many failed, and returns nothing. Add `failures => 'row'` to get the failed messages back too, with `status` `failed`, so you keep the answers you paid for. To keep every message with its answer, use decide. [filter](/functions/filter/) shows the command.
### rank
Every message in order, highest first ```sql SELECT answer AS urgency_rank, id AS msg_id, status FROM thinkthen.rank( 'SELECT msg_id AS id, body AS record FROM messages WHERE msg_id <= 3', 'How urgent is the request?', levels => ARRAY['routine', 'urgent']) ORDER BY urgency_rank; ``` Output ```text urgency_rank | msg_id | status --------------+--------+---------- 1 | 3 | answered 2 | 2 | answered 3 | 1 | answered (3 rows) ``` The double charge ranks first, the parcel second and the refund request last. `answer` is a `bigint` rank from 1, the most urgent first. `top => 2` keeps the first two. Failed messages come after the ranked ones, and `top` never drops them. [rank](/functions/rank/) shows the command.
### find
The line that answers in each message ```sql SELECT id AS msg_id, (answer).text AS requested_action, status FROM thinkthen.find( 'SELECT msg_id AS id, body AS record FROM messages WHERE msg_id = 5', 'Which line states the requested action?'); ``` Output ```text msg_id | requested_action | status --------+-------------------------+---------- 5 | Please refund my order. | answered (1 row) ``` The second line of message 5 states what the customer wants. `answer` is a `thinkthen.span` with the line's `text`, `start_offset` and `end_offset`. Offsets count characters from 0, and the end is not part of the line. When no line answers, `answer` is NULL and `status` is `answered`. [find](/functions/find/) shows the command.
### annotate
Several questions about each message at once ```sql SELECT id AS msg_id, (answer ->> 'refund')::boolean AS is_refund, answer ->> 'topic' AS topic, (answer ->> 'urgency')::float8 AS urgency FROM thinkthen.annotate( 'SELECT msg_id AS id, body AS record FROM messages WHERE msg_id <= 3', toolbox => '{ "refund": {"function": "decide", "question": "Does the customer request a refund?"}, "topic": {"function": "choose", "question": "What is the message about?", "choices": ["billing", "shipping", "other"]}, "urgency": {"function": "score", "question": "How urgent is the request?", "levels": ["routine", "urgent"]} }'); ``` Output ```text msg_id | is_refund | topic | urgency --------+-----------+----------+--------- 1 | t | billing | 0.05 2 | f | shipping | 0.14 3 | t | billing | 0.45 (3 rows) ``` Each message gets all three answers from one query. `toolbox` names each question, and rank can't be one of them. `answer` is a `jsonb` object with each question's answer by name. A value is JSON null when that question was not sure or failed, and `detail` holds each question's full outcome. [annotate](/functions/annotate/) shows the command.
### recognize
The names of the kinds you ask for ```sql SELECT names.id AS msg_id, found.text AS name, found.kind FROM thinkthen.recognize( 'SELECT msg_id AS id, body AS record FROM messages WHERE msg_id = 6', kinds => ARRAY['person', 'organization']) AS names LEFT JOIN LATERAL unnest(names.answer) AS found ON true; ``` Output ```text msg_id | name | kind --------+---------------+-------------- 6 | Morgan | person 6 | Example Group | organization (2 rows) ``` Message 6 names a person, Morgan, and an organization, Example Group. `answer` is an array of `thinkthen.recognized_name`, each with `text`, `kind`, `start_offset`, `end_offset` and `probability`. `LEFT JOIN LATERAL unnest(...)` lists each name on its own, and keeps a message that has none. [recognize](/functions/recognize/) shows the command.
### relate
How the names in each message connect ```sql SELECT links.id AS msg_id, (link.source).text AS person, link.relation, (link.target).text AS organization FROM thinkthen.relate( 'SELECT msg_id AS id, body AS record FROM messages WHERE msg_id = 5', rules => ARRAY[ROW('works_for', 'person', 'organization', NULL, false)::thinkthen.rule]) AS links LEFT JOIN LATERAL unnest(links.answer) AS link ON true; ``` Output ```text msg_id | person | relation | organization --------+--------+-----------+--------------- 5 | Morgan | works_for | Example Group (1 row) ``` Morgan works for Example Group, so relate links the two names. A rule is `ROW(relation, source kind, target kind, reads, either)::thinkthen.rule`, and `reads` may be NULL. `answer` is an array of `thinkthen.relation`, each with `source`, `target`, `relation`, `probability` and `either`. [relate](/functions/relate/) shows the command.
## Let other roles call the functions `CREATE EXTENSION` takes the functions away from every role but the administrator's. Grant each role the schema and each function it calls. Only an administrator can set `thinkthen.allowed_backends`, which lists the services and models a role may use, and without it a role may use only the default: ```sql wrap CREATE ROLE app LOGIN; GRANT USAGE ON SCHEMA thinkthen TO app; GRANT EXECUTE ON FUNCTION thinkthen.decide TO app; GRANT SELECT ON messages TO app; ALTER ROLE app SET thinkthen.allowed_backends = 'systemone:*'; ``` The role `app` can now call decide on `messages`, and any other function raises `permission denied`. ## Every option and setting The [PostgreSQL guide](https://github.com/botassembly/thinkthen/blob/v0.2.0/docs/surfaces/postgresql.md) lists every argument, result column, setting and error, such as `field => '/text'`, which judges one field of a JSON message, and `params`, which binds values to the `$1` in your query. The [extension's source](https://github.com/botassembly/thinkthen/tree/v0.2.0/extensions/postgres) shows how each call works.