Writing a query in SQL

In SQL mode you write the query yourself. Anything PostgreSQL can do while reading is allowed: WITH chains, window functions, GROUP BY combinations. The builder has no vocabulary for those, which is why SQL remains the real form of a query tag.

The point everything turns on: two spellings

TrakState stores a query with physical names. Stored, a record type is called rt_ plus 32 hexadecimal characters and a field f_ plus 32 hexadecimal characters. Those names hang on the identity and survive every rename — right for storage, unreadable for a person:

SELECT f_3a9b1c…::numeric AS actual_runtime FROM rt_c81f… WHERE f_11ab… >= $1

Displayed, the same query appears in the readable spelling. Every physical name is shown as the business name it stands for, in double quotes:

SELECT "ProductionReport.ActualRuntime"::numeric AS actual_runtime
FROM "ProductionReport"
WHERE "ProductionReport.Timestamp" >= $1

That is why queries used to be unreadable. What you see and edit in the editor is always the readable spelling; the translation happens on the server, exactly once, on the way in.

The rules of the spelling

• Double quotes are a name. In PostgreSQL "…" is always an identifier and never a text. A text is single-quoted. That is why the translation back cannot touch anything that is not a name in the first place.
• A field is a single identifier, "ProductionReport.GoodParts", not "ProductionReport"."GoodParts". That way it stands correctly everywhere, including inside a CTE where the table is no longer in scope.
• An unknown name is refused, not guessed. Write "Foo.Bar" when this tenant has no such field and TrakState refuses to save, naming the name.
• A name without a dot is left alone unless it happens to be a record type. AS "Value" is an output column name and is meant to stay exactly that.

When a field has been deleted

If a field is deleted after the query was written, there is no business name for its physical name any more. It then stays as f_…, and a note above the editor says which names those are. The query still runs; nothing is invented.

The editor

The editor colours PostgreSQL and completes from your own tenant: the record types, their fields in the readable spelling, the declared parameters and the common keywords. A field is inserted with its quotes, because that is the only spelling that resolves.

The check does not come from the editor but from the database itself: Try it out runs the query and puts a syntax error as a marker under the word PostgreSQL points at. See The dry run.

What a query tag may not do

Write. A query runs in a read-only transaction; a write attempt is rejected by the database, not by a text scan. That holds for the dry run exactly as it does in live operation.

And: a change here drops the builder’s description, if there was one (see Building a query with the builder).