Hacker News: bchammer

New comment by bchammer in "Composable SQL"

bchammer — Thu, 30 Jan 2025 04:35:05 +0000

Oracle Database can do that with a feature called 'SQL Macros'

https://docs.oracle.com/en/database/oracle/oracle-database/2...

SQL> create table test_pallets (pallet_id number);

Table created.

SQL> insert into test_pallets values (1),(2),(3),(4);

4 rows created.

SQL> create table test_boxes (pallet_id number, mass decimal);

Table created.

SQL> insert into test_boxes values (1,10),(1,20),(2, 5),(4, 5),(4, 50);

5 rows created.

SQL> create function pallet_payload_mass( 2 p dbms_tf.table_t, 3 b dbms_tf.table_t 4 ) return clob SQL_Macro as 5 begin 6 return q'{ 7 select 8 p.pallet_id, 9 coalesce(sum(b.mass), 0) as payload_mass 10 from 11 p 12 left outer join 13 b on b.pallet_id = p.pallet_id 14 group by 15 p.pallet_id 16 }'; 17 end; 18 /

Function created.

SQL> select payload_mass, pallet_id 2 from pallet_payload_mass(test_pallets, test_boxes) 3 order by payload_mass;

PAYLOAD_MASS PALLET_ID ------------ ---------- 0 3 5 2 30 1 55 4

New comment by bchammer in "New free developer version of the next-gen Oracle Database 23c!"

bchammer — Wed, 05 Apr 2023 17:02:05 +0000

Oracle is now the leading JSON database, not only with a binary JSON datatype (allowing true piece-wise updates on disk and fast 'jump-navigation for super fast queries), but also JSON Schema support (for schema enforcement, type casting, to describe database objects and auto-generate relational views over JSON) and with JSON Duality it's possible to access (read/write) the same data as table as well as as (MongoDB compatible) collections.

New comment by bchammer in "PRQL – A proposal for a better SQL"

bchammer — Mon, 24 Jan 2022 21:46:32 +0000

This looks like a turing-complete language if you add function. One benefit or current SQL is that it can (and is) often rewritten to be executed more efficiently. This requires a simpler and declarative model - far from a programming language.

If you want simple 'forward declaration' take a look at the SQL WITH clause.