Show HN: Trilogy – A Reusable, Composable SQL Experiment

always appreciate links to good tools

TBH, I don't think your test is very useful in real world environments. That is, you have 2 independent tables, and you're wanting the solution to depend on the fact that there are columns that are named the same across both tables.

IMO these kinds of "shortcuts based on column naming across tables" usually end in disaster down the road. For example, I've been bitten in the past by "natural joins" when we've wanted to refactor something later.

I definitely agree that I don't want to have to repeat logic within a single table, but the kind of syntactic sugar that is your litmus test is a big foot gun IMO.

> IMO these kinds of "shortcuts based on column naming across tables" usually end in disaster down the road.

But sometimes this decision has been made years ago and it's not realistic to change it now. I've wanted to do this many times, and I've never been the person who created said tables.

Also, certain use cases perform much better if you create temp tables with small subsets of data from the main tables. It sure would be nice to be able to reuse fragments of SQL written against the main tables... if only SQL were better.

Oh, I 100% agree with you.

> IMO these kinds of "shortcuts based on column naming across tables" usually end in disaster down the road.

I can see that point, and that was not what I wanted to express with my litmus test. It's only supposed to be a litmus test after all. In a proper solution there would be additional things I would be looking for, but so far everything I've seen already fails that "trivial" test.

One could easily re-formulate it, so that in the one tabel the column is named ".firstname", and in the other one it is named ".first_part_of_the_name".

The core point is more that no matter the relational logic you layer on top of a table/view, that logic should be paramterizable by table/view/column names, to be properly relocatable. I'd be happy about suggestions for better examples! Some solutions (I think dbt) do have some relocateability across schemas, but usually in a more singleton-like manner rather than being able to instantiate the logic multiple times.

I can just tell you that I interact with queries that would benefit from such kind of reuse on a daily basis. One common thing would also be mechanisms that you want to reuse across many different tables in your schema. E.g. soft-deletes or historic/snapshot tables. Nowadays those kinds of solutions usually end up being expressed in the ORM/query builder of a programming language (and thus highly fragmented across programming language ecosystems), instead of living on an SQL-like level and being able to mature better.

Hi,

Having this sort of "table polymorphism" is something we've thought a lot about for PRQL and is definitely something we want to get right. That said it's not straightforward but you can do a lot of it already. You can try the following examples for yourself in the PRQL Playground (https://prql-lang.org/playground/).

First a simple example using functions as they are documented:

```prql

let fullname = func firstname lastname -> f"{firstname} {lastname}"

from customers

select full_name=(fullname first_name last_name)

```

Now the example above isn't quite what you're looking for because you still have to specify the columns as function arguments and there really isn't much gained here. It serves to illustrate the principle though as the `fullname` function could be doing something more complicated.

What you want is:

```prql

let add_full_name = func tbl -> (

    from tbl

    derive full_name = f"{first_name} {last_name}"

    )

from customers

add_full_name

select full_name

```

Now this requires the `` type annotation which hasn't been documented because it's still quite experimental. However this works right now and can be applied to different tables or relations, for example you could use the same function in the following:

```prql

from i=invoices

join c=customers (==customer_id)

select {c.first_name, c.last_name, i.total}

sort {-total}

add_full_name

select {full_name, total}

```

I'll add some more examples in child comments.

Disclaimer: I'm a PRQL contributor.