psql=# select ( 𝞴 ??? ); · functional programming … in sql ? 𝞴 ⦁ ??? maslow’s...
TRANSCRIPT
![Page 1: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/1.jpg)
𝞴 ⦁ ???
psql=# select ( 𝞴 . ??? );
Functional Programming … in SQL ?
![Page 2: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/2.jpg)
𝞴 ⦁ ???
Maslow’s Hierarchy of Lambda Jam
Idris / Agda / Coq
Haskell / OCaml
Scala / Clojure
…
JavaScript / SQL
C / C++ / Assembly
![Page 3: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/3.jpg)
𝞴 ⦁ ???
Functional Programming… in SQL ?
![Page 4: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/4.jpg)
𝞴 ⦁ ???
“… that way I can just treat this likea simpleton manager of flat files.”
specialised tool fordata munging
general purposeprogramming environment
“I think I’ll do all my complexdata munging over here…”
![Page 5: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/5.jpg)
𝞴 ⦁ ???
Mt Enterprise
“thou shalt haven tiers,
andin the middle one
doest thou allthe real work”
![Page 6: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/6.jpg)
𝞴 ⦁ ???
Functional Programming… in SQL ?
![Page 7: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/7.jpg)
𝞴 ⦁ ???
select to_json( ( username, given_name, family_name, user_type ) :: api.user ) from app_user;
{ "username": "bruce", "givenName": "Bruce", "familyName": "Lee", "userType": "power"}
![Page 8: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/8.jpg)
𝞴 ⦁ ???
select to_json(api.user('sam')) as user_details, api.whitelabel_theme('unbranded') as brand_theme, api.account_unread_messages('customer1') as unread_messages;
![Page 9: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/9.jpg)
𝞴 ⦁ ???
select p.name, p.dob, a.reason, a.date from patient p left join lateral last_appointment( p.patient_id ) a on true;
create function last_appointment( pid bigint ) returns table(...)as $$ select * from appointment a where a.patient_id = pid order by a.date desc limit 1;$$ language sql stable;
![Page 10: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/10.jpg)
𝞴 ⦁ ???
Approaching SQL functionally
1. “functional core, imperative shell”— heavy lifting at the outermost level …— … and inside that, referentially transparent functions
2. functions of one SQL statement— the SQL equivalent of a single Lisp / lambda calculus
expression— 'language sql', not 'language plpgsql'
![Page 11: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/11.jpg)
𝞴 ⦁ ???
So, FP == map / reduce, yeah?
![Page 12: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/12.jpg)
𝞴 ⦁ ???
map :: (a -> b) -> [a] -> [b]
select f(a) from source a;
![Page 13: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/13.jpg)
𝞴 ⦁ ???
select a from source a where f(a);
filter :: (a -> Bool) -> [a] -> [a]
![Page 14: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/14.jpg)
𝞴 ⦁ ???
select avg(a) from source a;
reduce :: ((a -> b' -> b'), b', (b' -> b)) -> [a] -> b
![Page 15: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/15.jpg)
𝞴 ⦁ ???
select a from source_1 a union allselect b from source_2 b;
(++) :: [a] -> [a] -> [a]
![Page 16: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/16.jpg)
𝞴 ⦁ ???
select b from source a join lateral ( select f(a) ) b on true;
concatMap :: (a -> [b]) -> [a] -> [b]
![Page 17: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/17.jpg)
𝞴 ⦁ ???
concatMap lateral join example
select o.entrant from competition c join lateral ( select repeat( c.entrant, c.count ); ) o on true;
entrant | count --------|------ john | 3 bob | 0 alice | 1
entrant ------- john john john alice
![Page 18: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/18.jpg)
𝞴 ⦁ ???
How to do FP in SQL
1. write (mostly) pure functions— pure functions for data transformation ('select')— pure functions for predicates ('where')— impure functions for dataset-returning functions ('from')
2. compose them
![Page 19: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/19.jpg)
𝞴 ⦁ ???
create function full_name( given_name text, family_name text) returns textas $$ select given_name || ' ' || family_name;
$$ language sql immutable;
create function full_name( pid bigint
) returns textas $$ select given_name || ' ' || family_name from person where person_id = pid;$$ language sql stable;
✔ ✘
![Page 20: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/20.jpg)
𝞴 ⦁ ???
select to_person_json(p) from person p;
create function to_person_json( p person ) returns jsonb as $$ select jsonb_build_object( 'id', p.id, 'name', full_name( p.given_name, p.family_name ), ... ); $$ language sql immutable;
✔
![Page 21: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/21.jpg)
𝞴 ⦁ ???
create function early_adopter( id bigint ) returns booleanas $$ select id < 100;$$ language sql immutable;
explain analyze verbose select * from se_user where id < 100;
explain analyze verbose select * from se_user where early_adopter( se_user_id );
![Page 22: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/22.jpg)
𝞴 ⦁ ???
select g(b) from ( select f(a) from a ) b;
select g( f(a) );
map g . map f == map (g . f)
![Page 23: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/23.jpg)
𝞴 ⦁ ???
select a2 from ( select a1 from src a1 where f(a1) ) a2 where g(a2);
select a from src a where f(a) and g(a);
filter g . filter f == filter (f &&& g)
![Page 24: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/24.jpg)
𝞴 ⦁ ???
select c from ( select g(a) from src a ) b join lateral ( select f(a) ) c on true;
select c from src a join lateral ( select f(g(a)) ) c on true;
concatMap f . map g == concatMap (f . g)
![Page 25: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/25.jpg)
𝞴 ⦁ ???
Doing it right: organise like real code
● for re-use, separation-of-concerns, abstraction, etc— extract functions— … including table-returning functions— use views (including updateable views)— use common table expressions (CTEs – 'with' clauses)
● with an API
● with tests ! ! !
![Page 26: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/26.jpg)
𝞴 ⦁ ???
Beyond: model effects & change as data
● model time explicitly
● try to model irrefutable (therefore immutable) facts
● if the business process is that user creation sends an email, insert into an email_out table as part of the transaction— listen / notify is your friend
![Page 27: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/27.jpg)
𝞴 ⦁ ???
How much app layerdoes a CRUD app really need
when the database can alreadymunge data and do JSON?
Taking SQL-as-the-app-layer to extremes
![Page 28: psql=# select ( 𝞴 ??? ); · Functional Programming … in SQL ? 𝞴 ⦁ ??? Maslow’s Hierarchy of Lambda Jam Idris / Agda / Coq Haskell / OCaml Scala / Clojure](https://reader030.vdocuments.us/reader030/viewer/2022040617/5f2141e9f950296b4e57116c/html5/thumbnails/28.jpg)
𝞴 ⦁ ???
pg_crud_ops
[{ "school": { "get_school": {} }, "classes": { "get_classes": {} }, "stats": { "get_statistics": { "start_date": "2018-12-01", "end_date": "2018-12-08" } }}]
select api_student.get_school();
select api_student.get_classes();
select api_student.get_statistics ( "start_date" => "2018-12-01", "end_date" => "2018-12-08");