Skip to main content

Parameters

Parameters let one query serve many inputs. Write $1, $2, ... where a value would go, store defaults with the query, and override any of them at execution time. Parameter values never become part of the SQL text: they are bound separately, so a value can never change what the query does.

SELECT variable, value FROM device($1) AS d
WHERE value > $2
ORDER BY time DESC LIMIT 50

Here $1 picks which of your devices to query and $2 is the threshold. Execute it with different values each time without touching the query.

Where you can use them​

  • In place of values: comparisons (value > $1), IN lists, BETWEEN bounds, and LIKE/ILIKE patterns.
  • As the device or entity id: device($1) and entity($1) let one stored query target different resources per execution. The id goes through the same ownership check as a literal id; a device you do not own fails with 404.

Supplying values​

Parameters travel as an array of { key, value } pairs, and all values are strings. The server converts each one based on where it is used (number, timestamp, boolean, text):

{
  "params": [
    { "key": "$1", "value": "DEVICE_ID" },
    { "key": "$2", "value": "30" }
  ]
}
  • Stored defaults must cover every placeholder the query uses.
  • At execution time you can override any subset: values you send win, stored defaults fill the rest.

Paginating with parameters​

LIMIT and OFFSET take fixed numbers in the query text, and a stored query has fixed text, so a classic skip pattern (LIMIT 100 OFFSET 200) would need one stored query per page. Page through a parameter cursor instead: keep the LIMIT fixed and pass the last timestamp you saw as $1.

SELECT variable, value, time FROM device('DEVICE_ID') AS d
WHERE time < $1
ORDER BY time DESC LIMIT 100
{ "params": [{ "key": "$1", "value": "2026-07-17T10:00:00Z" }] }

The cursor form is also the faster one: a deep OFFSET makes the database scan and discard every skipped row on each page, while the cursor jumps straight to the right position. Prefer it for anything beyond a few pages. Fleet queries (device_data_by_tag) paginate over devices with the after_device body field instead; see Executing Queries.

Rules​

  • Only $n placeholders, numbered 1..N with no gaps. Named (:name) and anonymous (?) styles are rejected.
  • Dates accept ISO format (2026-01-01 or 2026-01-01T00:00:00Z); booleans accept true/false/1/0.
  • An invalid value returns a 400 naming the placeholder (for example, Invalid value for parameter $1); the value itself is never echoed back.