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.

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

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, check it as Installation shows, and copy its files into the server's folders:

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 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 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:

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

 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:

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

 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 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

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

 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 shows the command.

choose

One of your choices for each message

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

 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 shows the command.

tag

Every label that fits each message

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

 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 shows the command.

score

A number from 0 to 1 for each message

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

 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 shows the command.

filter

Only the messages that answer yes

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

 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 shows the command.

rank

Every message in order, highest first

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

 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 shows the command.

find

The line that answers in each message

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

 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 shows the command.

annotate

Several questions about each message at once

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

 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 shows the command.

recognize

The names of the kinds you ask for

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

 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 shows the command.

relate

How the names in each message connect

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

 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 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:

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 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 shows how each call works.