What to replace open_meta() and commodity_meta() and similar with

[dnicolodi]

Working toward the goal on making beanquery a general purpose SQL-like query language for arbitrary data and make the BQL language more regular, we need to decide what to do with the current BQL function that depend on the row context, in particular the open_meta(), commodity_meta() and similar functions.

In an unpublished branch I already implemented structured types (or composite types in PostgreSQL parlance https://www.postgresql.org/docs/14/rowtypes.html) thus one way to solve the problem would be to make accounts and commodities their own structured types and allow a syntax similar to

SELECT
  account.meta["foo"],
  position.units.commodity.meta["bar"]
FROM
  postings

Another way is to rely on sub-queries to do the lookup:

SELECT
  (SELECT meta["foo"] FROM accounts WHERE account = p.account),
  (SELECT meta["bar"] FROM commodities WHERE commodity = p.position.units.commodity),
FROM
  postings AS p

A solution based on a join

SELECT
  account,
  position,
  commodities.meta["foo"]
FROM
  postings, commodities
WHERE
  commodities.account = postings.account

would not work because commodities is a table of all the commodities definitions, and not all the commodities used in a Beancount ledger need to defined, thus the above query would return only a subset of the postings.

The BQL syntax for the first solution is definitely more attractive but it is less flexible and would require making the BQL types system more complex to allow accounts and commodities to be at the same time strings and structured types, unless explicit type conversions are required. Something like

SELECT
  account(account).meta["foo"],
  commodity(position.units.commodity).meta["bar"]
FROM
  postings

is not horrible but feels a bit redundant. Going the other way around:

SELECT *
FROM
  postings
WHERE
  account = account("Assets:Test")

is not much better.

The syntax for the second solution is verbose, but it is the most explicit and it allows for the most flexibility.

Comments?

[blais]

I like your proposed solution, the one in the block before last.
I think one of the things to keep in mind throughout all this - and I believe you are from the commentary - is that eventually beanquery should be liftable from Beancount context to more general tables of things. A powerful SQL swiss knife that can be applied on small datasets containing tables. I think what you propose is inline with that.