4 ms·

When viewed as a DSL for set theory, views, CTEs, set-returning functions, et al are indeed proper first-class query abstractions. When viewed through the lens

by ttfkam 3y ago

When viewed as a DSL for set theory, views, CTEs, set-returning functions, et al are indeed proper first-class query abstractions. When viewed through the lens of general purpose imperative or functional programming languages, it's easy to see how it can be seen as falling short. I'll admit much of the tooling and driver APIs leave a lot to be desired. Some tools do make good efforts though such as nested fragments in this driver. https://github.com/porsager/postgres#building-queries https://github.com/porsager/postgres#building-queries

naasking 3y ago
> When viewed as a DSL for set theory, views, CTEs, set-returning functions, et al are indeed proper first-class query abstractions. It's still lacking even then. You can't assign a set to variable for instance, and then use that variable in other definitions or queries.
- ttfkam 3y ago
  You can in Postgres. It's not often a good idea since it would constitute an optimization fence and potentially buffer large values in memory, but you can absolutely do this in a function variable. record[] some_table_name[] jsonb containing an array of records And of course the most obvious shared set variable of all, supported by every popular RDBMS in existence: the temporary table. Something scoped to the current session, of essentially unlimited width and length, and accessible to other definitions and queries. We get it. You want something closer to a general purpose programming language. But don't confuse your preferences with actual missing features.
  - naasking 3y ago
    Temp tables are another bandaid. Like views, they are another second-class abstraction intended to address the lack of generality of relations. As you hint, adding second-class features is intended to handle pain points while keeping optimization simple, but it's the wrong way to do it IMO. No fixed number of second class features can make up for the lack of relations as first-class values, so this is not just a matter of preference. I actually don't want a general purpose programming language as that's not suitable for data access, I just want a query language that doesn't impose artificial limitations on relations. Edit: although I will admit that programming language theory is a hobby, so that's why I understand the source of the pain every time I have to use SQL. LINQ almost gets this right, but is still a huge improvement on SQL.
    - ttfkam 3y ago
      How would LINQ make jsonpath queries or unroll arrays on the database with unnest?
      - naasking 3y ago
        LINQ in EntityFramework (EF) supports querying JSON columns as objects [1]. unnest support is potentially on the horizon for EF core for PG [2], but already supported for Couchbase [3]. [1] https://learn.microsoft.com/en-ca/ef/core/what-is-new/ef-core-7.0/whatsnew#json-columns https://learn.microsoft.com/en-ca/ef/core/what-is-new/ef-cor... [2] https://github.com/npgsql/efcore.pg/issues/1525 https://github.com/npgsql/efcore.pg/issues/1525 [3] https://github.com/couchbaselabs/Linq2Couchbase/blob/master/docs/unnest.md https://github.com/couchbaselabs/Linq2Couchbase/blob/master/...

by ttfkam 3y ago

naasking 3y ago

> When viewed as a DSL for set theory, views, CTEs, set-returning functions, et al are indeed proper first-class query abstractions. It's still lacking even then. You can't assign a set to variable for instance, and then use that variable in other definitions or queries.

ttfkam 3y ago
You can in Postgres. It's not often a good idea since it would constitute an optimization fence and potentially buffer large values in memory, but you can absolutely do this in a function variable. record[] some_table_name[] jsonb containing an array of records And of course the most obvious shared set variable of all, supported by every popular RDBMS in existence: the temporary table. Something scoped to the current session, of essentially unlimited width and length, and accessible to other definitions and queries. We get it. You want something closer to a general purpose programming language. But don't confuse your preferences with actual missing features.
- naasking 3y ago
  Temp tables are another bandaid. Like views, they are another second-class abstraction intended to address the lack of generality of relations. As you hint, adding second-class features is intended to handle pain points while keeping optimization simple, but it's the wrong way to do it IMO. No fixed number of second class features can make up for the lack of relations as first-class values, so this is not just a matter of preference. I actually don't want a general purpose programming language as that's not suitable for data access, I just want a query language that doesn't impose artificial limitations on relations. Edit: although I will admit that programming language theory is a hobby, so that's why I understand the source of the pain every time I have to use SQL. LINQ almost gets this right, but is still a huge improvement on SQL.
  - ttfkam 3y ago
    How would LINQ make jsonpath queries or unroll arrays on the database with unnest?
    - naasking 3y ago
      LINQ in EntityFramework (EF) supports querying JSON columns as objects [1]. unnest support is potentially on the horizon for EF core for PG [2], but already supported for Couchbase [3]. [1] https://learn.microsoft.com/en-ca/ef/core/what-is-new/ef-core-7.0/whatsnew#json-columns https://learn.microsoft.com/en-ca/ef/core/what-is-new/ef-cor... [2] https://github.com/npgsql/efcore.pg/issues/1525 https://github.com/npgsql/efcore.pg/issues/1525 [3] https://github.com/couchbaselabs/Linq2Couchbase/blob/master/docs/unnest.md https://github.com/couchbaselabs/Linq2Couchbase/blob/master/...