EscribalMore
Database
This group reads data out of a PostgreSQL database and leaves it in the workflow, so a loop can write it into a document of any of the eight formats.
The connection is not part of the workflow, and that is the first thing to know here. A workflow names a connection and nothing else: there is no Server, no User and no Password box in it anywhere. The program asks for those when Run is pressed and hands them to that run alone. Two things follow from it, and both are the point: the same file can be given to somebody else, who puts in their own server and runs it; and no instruction can read a password, so none can write it into a document, export it, or compare it in an If.
Prerequisite: none for the reading itself. Putting what is read into a file needs a document, created before it by the Create instruction of its format.
Reading only, and it is the server that says so. The query runs inside a read-only transaction of PostgreSQL. The program does not read the query to judge it — the server refuses anything that would change data, which is what keeps true, word for word, the promise that a run which fails leaves nothing behind.
- Connection: the name written in the Connection box. Empty means the single connection of
the workflow, the same way an empty Document means the current document. Pressing Run
collects every distinct name the workflow uses and asks for each one. The name has to be written
in the box:
@namedoes not work here, because the program reads it out of the file before the run starts. - The details are asked for on every run, filled in with what was typed last while the program has been open, so one Enter starts it. They are never written to disk: closing the program forgets them. That is also how the same workflow is pointed at a test database first and at the live one afterwards.
- Everything comes back as text, in the shape Iterate rows gives in every other format: a
list of records, each field named by a column of the result. So For each,
@row.column, Aggregate list and Group and aggregate work on it with nothing new to learn. - Dynamic values
@namework in the Query box as in any other text box, so a query can be built out of what the workflow already knows. To write a real SQL parameter, double the sign:@@customerreaches the server as@customer. - Fields “from variable” are the same idea for the boxes that hold a number: Max rows (from variable) and Timeout (from variable) read their value from a saved name instead of the box.
- A value with an apostrophe in it breaks a query it is put into.
@namereplaces the text as it is, soWHERE name = '@customer'with a customer calledO'Brienreaches the server as a query it cannot read, and the workflow stops. Where the value comes from outside, it is safer to bring more rows and filter them with an If. - Stopping a run stops the query with it. Stop ends the engine, and the database drops the work it was doing for it; nothing was being written, so there is nothing half done.
Read query
Runs a SELECT and saves its rows as a list of records, one per row, named by the columns of
the result.
The connection is not in the workflow. This instruction names one and nothing else: the server, the database, the user and the password are asked for by the program when Run is pressed, and there is no box here to write them into. A workflow that carried them could not be shared, and inventing a parameter for them is how a report ends up unable to run at all.
When to use it: whenever the figures of a report live in a database instead of a file — the
invoices of a month, the tickets still open, what is left in a warehouse. The rows come back in the
shape a loop already knows, so the report is written with For each and @row.column exactly as it
would be from a CSV file.
| Parameter | Meaning | Default | Required |
|---|---|---|---|
| Connection | Which of the connections this run was given; empty is the only one | (empty) | No |
| Query | The SELECT to run; empty does nothing. Supports @name |
(empty) | Yes |
| Max rows | How many rows to bring at most; -1 brings all of them |
10000 |
No |
| Timeout (s) | How long to wait for the query before giving up | 30 |
No |
| Time zone | Which zone a date and time with a zone is shown in, by its name (Europe/Madrid); empty writes it in UTC. Supports @name |
(empty) | No |
| Max rows (from variable) | Reads the limit from a saved value | (empty) | No |
| Timeout (from variable) | Reads the wait from a saved value | (empty) | No |
| Save key | Name the list of rows is saved under | (empty) | Yes |
| Save count key | Name the number of rows is saved under | (empty) | No |
Important behavior: the columns of the result name the fields, so this is the one Iterate rows
with nothing to ask about a header row. Two columns called the same are the case to watch: the
second one is named column2 by its position, and the way out is an alias in the query
(SELECT total AS invoiced). A column whose name has a dot in it cannot be reached with
@row.a.b either, and the answer is again an alias.
A query that answers with no rows saves the list empty and says so, because an empty report is almost always a table name with a typo in it. More rows than Max rows are cut short, and that is said too. With Save key and Save count key both empty the instruction does nothing: going out to the network to throw the answer away is not worth doing.
How each kind of value is written. Everything becomes text, which is what a document holds, and each kind is written on purpose:
| Type in the database | Comes out as |
|---|---|
| integer, bigint, numeric, real, double precision | The number as it is; numeric(10,2) keeps its two decimals (1234.50) |
| boolean | true or false, in lower case — the words an If compares against |
| date | 2026-08-30 |
| time | 14:05:00 |
| timestamp | 2026-08-30T14:05:00, exactly as it was stored |
| timestamp with time zone | The instant in UTC (2026-08-30T12:05:00Z), or in the zone Time zone names (2026-08-30T14:05:00+02:00) |
| text, varchar, json, jsonb | The text as it is; a json field arrives as its own text |
| any array | The way PostgreSQL itself writes a list: {red,green} |
| uuid | 11111111-2222-3333-4444-555555555555 |
| interval | 1.00:00:00 — one day, as days.hours:minutes:seconds |
| NULL | Empty text |
| bytea | Empty text, and the run says which column it was |
A date and time with a zone deserves a word: PostgreSQL does not keep a zone in it, it keeps an
instant, so there is no offset to preserve — the +02 written when it was stored was used once and
dropped. Time zone says which zone to show the instant in, and because it lives in the workflow
the same file gives the same document whoever runs it. A name this computer does not know is said
out loud and the time is written in UTC.
A json field and a list arrive as text, which is enough to write them into a document. To walk
inside them, save the text and open it with Read from text of the JSON group.
Read single value
Runs a query and saves one value: the first column of the first row.
The connection is not in the workflow, the same as above: this instruction names one, and the server and the password are asked for when Run is pressed.
When to use it: for a count, a total, or a single name — how many invoices are unpaid, what the month adds up to, what the last customer is called. It is the short way when the answer is one figure and a list of rows would only have to be collapsed afterwards.
| Parameter | Meaning | Default | Required |
|---|---|---|---|
| Connection | Which of the connections this run was given; empty is the only one | (empty) | No |
| Query | The SELECT to run; empty does nothing. Supports @name |
(empty) | Yes |
| Timeout (s) | How long to wait for the query before giving up | 30 |
No |
| Time zone | Which zone a date and time with a zone is shown in, by its name (Europe/Madrid); empty writes it in UTC. Supports @name |
(empty) | No |
| Timeout (from variable) | Reads the wait from a saved value | (empty) | No |
| Save key | Name the value is saved under | (empty) | Yes |
Important behavior: only the first column of the first row is looked at, whatever else the query
answers with, so SELECT count(*) FROM invoices is the natural shape for it. A query that answers
with no rows saves nothing and says so.
An empty value —a NULL, which is what SELECT max(total) gives on a table with nothing in
it— saves nothing either, and says so: a figure nobody worked out must not be able to slip into a
document as a blank that reads like a real answer. The value is written exactly as the table above
says, so it goes into a paragraph, a cell or a file name as it is, and Calculate variable can
still read it back as a number when it is one.
This documentation is the help of the application, and it comes underCreative Commons Attribution 4.0.