News for hackers. Stuff that matters.
Re:What if an SQL statement returned a database? by klysm on Tuesday December 12, 2023 @08:33AM (#38611849)
I think this solves the problem of Cartesian explosions in ORMs. The requirement of related data needing to be shoved into a single table means data shared by many entities has to be duplicated. The result set being split into distinct tables removes this constraint.

The only way I know how to deal with this in Postgres while maintaining a single query is to lateral join into a json aggregate. However, I don’t think the tooling around that approach is mature enough yet.

Re:What if an SQL statement returned a database? by eddd-ddde on Tuesday December 12, 2023 @10:32AM (#38613470)
I love postgres json aggregates, but since postgres cant do nested aggregates it is kinda limiting when I would like to build my whole query response in a single postgres query
Re:What if an SQL statement returned a database? by twojacobtwo on Tuesday December 12, 2023 @12:03PM (#38614918)
I'm new to web-dev (and databases), so my apologies for the potentially silly question, but what's an example situation where you would use nested aggregates and what kind of effort would it save, if it were possible?
Re:What if an SQL statement returned a database? by 5636588 on Tuesday December 12, 2023 @12:58PM (#38615799)
Was curious myself so asked chatgpt and this what I got:

Nested aggregates in databases are useful when you want to perform calculations or summaries on groups of data within larger groups. For example, imagine you have a database of sales transactions with products and categories. You could use nested aggregates to find the total sales for each category and then calculate the average sales per category. It saves effort by allowing you to perform calculations at different levels of granularity in a single query.

Re:What if an SQL statement returned a database? by sargstuff on Tuesday December 12, 2023 @04:43PM (#38619047)
goal directed programming is the related non-sql programming paradigm. unicorn/icon[1]

----

Use cases:[2]

autoparts inventory (book keeping page statics of sales/sales person, parts, inventory on hand, etc)

internet statistics for through put on all interfaces on group of servers [0]

--- topic related books:

Joe Celko has book series 'SQL for Smarties' that covers basic through advanced aggrecation with sql using example queries.

Joe Celko's 'Trees and Hierarchies in SQL for Smarties' covers some of the ideas related to 'SQL statment returned a database'?

---

[0]: https://dba.stackexchange.com/questions/173044/nested-aggreg...

[1] : http://unicon.org/

[2] : https://cs.uwaterloo.ca/~tozsu/courses/CS338/lectures/6%20Ne...

Re:What if an SQL statement returned a database? by eddd-ddde on Tuesday December 12, 2023 @08:38PM (#38621560)
Personally, I prefer raw sql over something like an ORM. So many times I want to get all users, with all posts, with all post comments (example).

You can do an array aggregate to get all users and their posts in the array, with build_jsonb_object you can make the posts an object of say id and content. But then you can't really make another array aggregate per post.

Re:What if an SQL statement returned a database? by sargstuff on Tuesday December 12, 2023 @01:13PM (#38616027)
Can fake it with postgress copy/execute[0], where each aggregate block level is a transaction.

[0] : https://medium.com/r3d-buck3t/command-execution-with-postgre...

Re:What if an SQL statement returned a database? by sargstuff on Tuesday December 12, 2023 @06:27PM (#38620296)
shell out for nested aggregates[0]. one 'sql querry' to run a batch shell containing all multiple aggregate levels in a file, import back in to return table reference.

[0] : https://medium.com/r3d-buck3t/command-execution-with-postgre...

Re:What if an SQL statement returned a database? by klysm on Tuesday December 12, 2023 @09:49PM (#38622052)
You can do nested aggregates though unless I’m misunderstanding what you mean by nested aggregate
Re:What if an SQL statement returned a database? by programmarchy on Tuesday December 12, 2023 @10:15AM (#38613224)
I’ve been doing this. Curious what you mean by tooling. All the pg client libraries I’ve used support json/jsonb return types, for example.
Re:What if an SQL statement returned a database? by klysm on Tuesday December 12, 2023 @09:50PM (#38622055)
I don’t know any ORMs except Zapatos which does what I’m describing with lateral nested joins
Re:What if an SQL statement returned a database? by klysm on Wednesday December 13, 2023 @12:30AM (#38623075)
To elaborate, I want automatic strong typing for the query results