Declaring query result shape via Library.parameter with use = out

[niquola]

Summary

SQLQuery currently says nothing about the shape of a query's result. Library.parameter is constrained as input-only — parameter.use carries ^short = "in (query parameters are always input)" — and $sqlquery-run returns rows in a negotiated format whose columns are implied by the SQL and never declared.

For most queries that is fine. But some queries have a contract: a caller needs to know that a given query returns particular columns, of particular types, before running it. Today there is no way to state that, so it has to live in prose or be discovered by execution.

Proposal

Use Library.parameter with use = out to declare the output columns of a query.

This needs no extension and no new element. ParameterDefinition.use is bound to operation-parameter-use, which has exactly two codes, in and out; name, type, min and max are already there and mean the right things. The only change required in this IG is correcting the ^short on SQLQuery.parameter.use, which currently asserts something stronger than the profile enforces (use is not fixed, so out already validates).

# a query that declares two output columns
parameter:
  - name: threshold      # existing behaviour, unchanged
    use:  in
    type: decimal
  - name: value
    use:  out
    type: decimal
    min:  1
    max:  "1"
  - name: denominator
    use:  out
    type: integer
    min:  0
    max:  "1"

Why it matters

Declared outputs make a query composable and checkable without running it:

• Contracted queries. Any pattern where a family of queries must agree on a result shape — the data quality checks discussed in Discussion: FHIR-native data quality checks via SQLQuery Library #375 are one, but query-to-query composition and templated queries have the same need.
• Validation. A conformance test can assert a query conforms to its declared shape, rather than asserting an example output.
• Tooling. A catalog UI, a dashboard builder, or a code generator can bind to columns without executing arbitrary SQL first.
• SQLView composition. A consuming query references a view by label and then selects columns from it; today those column names are known only by reading the view's SQL.

Scope of the change

Small, and backwards compatible: no existing instance becomes invalid, because use = out is not currently used.

1. Correct the ^short on SQLQuery.parameter.use.
2. Add non-normative guidance describing what use = out means for a query result, and that output parameters are declarations, not values.
3. Optionally state whether a server MAY validate an actual result against the declared outputs, or whether the declaration is purely informative.

Open questions

• Should declared outputs be normative (a server SHALL reject a query whose result does not match) or informative? Informative is the cheaper start.
• Does SQLView need the same? It currently has parameter 0..0, which was chosen to keep parameterised views out of scope — but that also blocks it from declaring outputs, which is arguably the more useful half.
• Column order: ParameterDefinition has no ordinal. If order matters for a result format like CSV, is the order of parameter entries significant?

Raised while working through #375; the DQ checks there need exactly this, but the mechanism is general and seems worth having on its own terms.

[johngrimes]

It is also possible to use $sqlquery-run with DESCRIBE or DESCRIBE QUERY.

[johngrimes]

What do min and max mean on output columns?