As far as I can tell, the parent is describing something like a trait or interface: both tables have `.firstname: text` and `.lastname: text`. This may not really be relational -- I guess there should be a third table and they should both reference it -- but it does show up in real world schemas.

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.

> 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.

I think your blog post frames the problem very well!

Seeing that both someone working on PRQL and Malloy replied and to both of you it's an understood pain makes me feel a lot better about the future of these tools! When talking about that with people that are not that deep into the problem it is often hard to transport the difference between this kind of composability vs. the composability that the tools are offering today, and the implications that come with that.

At a past startup I had the fortune to be able to work on a similar system to what I am looking for: Packageable, reusable relation algebra inspired by Substrait. It had the downside though that it was quite tied to RDF and SPARQL in its implementation, and now I'm chasing something similar in the SQL world :D

I have used CTEs with dynamic query stitching to solve this problem (specifically my business operates over two very similar but distinct domains which we keep in separate buckets). If you build the majority of your logic into a CTE that processes named columns coming out of a prior chunk you can swap out what actual columns in the DB are mapped into the columns coming out of that earlier CTE with its definition. It may be possible to make this more magical using pl/pgsql but I've found that dynamic query stitching at the CTE resolution is a level of fiddly-ness I'm comfortable building into resilient products.

I work with complex data models and keeping all that structure in my brain takes enough effort that I want to keep my queries as simple as possible because when it's time to debug one there's no way I'm carrying over _any_ memory from when I originally wrote it.

> There needs to be a way to write a query that runs on an abstract interface, rather than an actual table.

Proper use of SQL inverts control. Instead of parameterizing query by table, you write a query and at the actual use site you join it on the table you need by fields your query provides. VIEWs allows you to not repeat yourself too often.

Best thing is that you do not need to even mention that "abstract interface table" as a parameter at all.

Do they ever write plpgsql? If not why not?

Here's a function to normalize values relative to the column range:

```prql

let normalize = func x -> ((x - min x)/((max x) - (min x)) | math.round 2)

from tracks

take 5

derive {ms_norm=(normalize milliseconds), bytes_norm=(normalize bytes)}

select {track_id, ms=milliseconds, ms_norm, bytes, bytes_norm}

```

which produces the following SQL:

```sql

WITH table_0 AS (

  SELECT
    track_id,
    bytes,
    milliseconds

  FROM
    tracks

  LIMIT
    5

)

SELECT track_id,

  milliseconds AS ms,

  ROUND(
    (milliseconds - MIN(milliseconds) OVER ()) / (
      MAX(milliseconds) OVER () - MIN(milliseconds) OVER ()
    ),
    2
  ) AS ms_norm,

  bytes,

  ROUND(
    (bytes - MIN(bytes) OVER ()) / (MAX(bytes) OVER () - MIN(bytes) OVER ()),
    2
  ) AS bytes_norm

FROM table_0

-- Generated by PRQL compiler version:0.13.2 (https://prql-lang.org)

```

Thanks for the thorough reply! Really glad to see at least one of the solutions working towards it, and I'll definitely check PRQL out again in more depth!

Here's an example of what this would look like in practice; https://gist.github.com/greenmtnboy/580f479c80e23c5362a70b43...

Sometimes you want to be able to do something like "run some SQL, but instead of using the normal tables use these temp tables I just created."

In particular, I wanted to do this in SQLite recently. I wanted to have one write process which would always remain unblocked. And I also wanted to be able to run certain tasks which would do some temporary/discardable DB manipulations as part of producing an output file. These tasks could open the SQLite DB in read-only mode; load relevant data into temp tables, manipulate that data, and write the output file. Everything would have worked great if only SQL were a more composable language.

Sure, I could do that in Zillion the same way I'm currently composing queries in Python at runtime with PyPika.

I'm looking for a more programming language agnostic solution that tools like this (e.g. also PRQL, Malloy) usually offer.

See my reply to hn_throwaway_99. The example is overly trivialized and should expand to more complex relational logic as well.

Their examples showing why Trilogy is so good are comparing it against poorly written SQL with poorly designed schemas. It reduces my confidence that their tool is actually solving real problems but instead was borne out of frustration in learning SQL and databases.