squeal-postgresql-qq 0.1.1.0 → 0.1.1.1
raw patch · 4 files changed
+186/−19 lines, 4 filesPVP ok
version bump matches the API change (PVP)
API changes (from Hackage documentation)
Files
- README.md +40/−15
- changelog.md +126/−0
- squeal-postgresql-qq.cabal +3/−2
- test/test.hs +17/−2
README.md view
@@ -51,27 +51,61 @@ * Prepared statement parameters * See the haddock documentation for how to get haskell values into your sql statements.-* `WITH` clauses (Common Table Expressions)-* `ON CONFLICT` clause+* `ON CONFLICT` clause for `INSERT` statements. -## Other features is not currently implemented+## Other features not currently implemented -(Generated by an LLM. Maybe not complete.)+This is a list of known unsupported SQL features. If you need one of+these, please open an issue! +This list was generated by an LLM, and may not be complete.++### General Query Structure * `TABLESAMPLE` clause * `ONLY` keyword for table inheritance * `WINDOW` clause and window functions (`OVER`) * `INTO` clause (`SELECT ... INTO ...`)+* `ORDER BY USING`+* `FOR READ ONLY` locking clause+* `FOR UPDATE/SHARE OF` with qualified table names.+* `WHERE CURRENT OF` for cursors+* Aliasing a `JOIN` clause directly (e.g., `(SELECT * FROM t1 JOIN t2 ON ...) AS myalias`)+* `NATURAL JOIN`+* `USING` join qualification (e.g., `JOIN ... USING (col)`)+* `LIMIT` with comma offset (e.g. `LIMIT 10, 20`)+* `LIMIT ALL`+* `FETCH` clause+* Advanced `GROUP BY` features (`GROUPING SETS`, `CUBE`, `ROLLUP`)+* Multi-row `VALUES` clause (e.g. `VALUES (1, 'a'), (2, 'b')`)++### Common Table Expressions (WITH clauses)+* Recursive `WITH` clauses (`WITH RECURSIVE ...`)+* `MATERIALIZED` / `NOT MATERIALIZED` hints+* Column lists for CTEs are only partially supported (e.g., not for top-level `SELECT` statements)+* Data-modifying statements (`INSERT`, `UPDATE`, `DELETE`) within a `WITH` clause++### Data Manipulation (INSERT/UPDATE/DELETE)+* `INSERT ... DEFAULT VALUES`+* `INSERT INTO table (columns) SELECT ...` (must omit column list)+* `OVERRIDING` clause for identity columns in `INSERT`+* Column indirection in `INSERT` target lists (e.g., `INSERT INTO tbl (col[1]) ...`)+* Complex relation expressions in `UPDATE` or `DELETE` targets+* Column indirection in `UPDATE SET` clauses (e.g., `UPDATE tbl SET col[1] = ...`)+* `UPDATE` with multiple-column `SET` (e.g. `SET (a,b) = (1,2)`)++### Expressions and Functions * `LIKE` with `ESCAPE`-* `IN` with a subquery * `OPERATOR()` syntax * Parameter indirection (e.g., `$1[i]`) * Indirection on parenthesized expressions (e.g., `(expr)[i]`) * Aggregate `FILTER` clause-* `WITHIN GROUP` clause+* `WITHIN GROUP` clause for aggregates * `DISTINCT` in function arguments * `ORDER BY` in function arguments * Function name indirection (e.g., `schema.func`)+* Named or colon-syntax function arguments (e.g. `my_func(arg_name => 'val')`)++### Types and Casting * `SETOF` type modifier * `BIT` and `BIT VARYING` types * `INTERVAL` with qualifiers@@ -79,15 +113,6 @@ * Qualified type names (e.g., `schema.my_type`) * `CURRENT_TIMESTAMP` with precision * Multidimensional arrays with explicit bounds-* `ORDER BY USING`-* `FOR READ ONLY` locking clause-* Advanced `GROUP BY` features (`GROUPING SETS`, `CUBE`, `ROLLUP`)-* Multi-row `VALUES` clause-* `OVERRIDING` clause for identity columns-* Column indirection in `INSERT` target lists-* `WHERE CURRENT OF` for cursors-* Complex relation expressions in `UPDATE` or `DELETE` targets-* Column indirection in `UPDATE SET` clauses ## Supported features.
+ changelog.md view
@@ -0,0 +1,126 @@+### 0.1.1.1++No feature or behavior changes. Only documentation.++### 0.1.1.0++* Support common table expressions (CTEs).+ * `with users_cte as (select * from users) select * from users_cte` [✔]+ * `with users_cte as (select * from users), emails_cte as (select * from emails) select users_cte.*, emails_cte.email from users_cte join emails_cte on users_cte.id = emails_cte.user_id` [✔]+ * `with new_user (id, name, bio) as (values ('id_new', 'new_name', 'new_bio')) insert into users_copy select * from new_user` [✔]+ * `with to_delete as (select id from users where name = 'Alice') delete from users where id in (select to_delete.id from to_delete)` [✔]+ * `with to_delete as (select id from users where name = 'Alice') delete from users using to_delete where users.id = to_delete.id` [✔]+ * `with to_update as (select id from users where name = 'Alice') update users set name = 'Alicia' from to_update where users.id = to_update.id` [✔]++* Support `IN` subqueries.+ * `select * from users where users.id in (select emails.user_id from emails)` [✔]++### 0.1.0.0++Initial release, supports the following features (taken from the test suite output):++* queries+ * `select * from users` [✔]+ * `select * from public.users` [✔]+ * `SELECT * FROM "users" AS "users"` [✔]+ * `select * from users where name = 'bob'` [✔]+ * `select users.name from users` [✔]+ * `select name from users` [✔]+ * `select count(*) from users group by ()` [✔]+ * `select name, id from users` [✔]+ * `select id, name from users` [✔]+ * `select users.id, employee_id from users` [✔]+ * `select users.* from users` [✔]+ * `select users.* from other.users` [✔]+ * `select * from users limit 3` [✔]+ * `select * from users limit inline(lim)` [✔]+ * `select * from users offset inline(off)` [✔]+ * `select * from users offset 1` [✔]+ * `select users.id, employee_id as emp_id from users` [✔]+ * `select users.id as user_id, employee_id from users` [✔]+ * `select users.id from users left outer join emails on emails.user_id = users.id` [✔]+ * `select users.id, users.name, emails.email from users left outer join emails on emails.user_id = users.id where emails.email = inline("targetEmail")` [✔]+ * `select 'text_val'` [✔]+ * `select 1` [✔]+ * `select 1 AS num, 'text_val' AS txt` [✔]+ * group by+ * `select name from users group by name` [✔]+ * `select employee_id, count(id) from users group by employee_id` [✔]+ * `select employee_id, name, count(id) from users group by employee_id, name` [✔]+* inserts+ * `insert into emails (id, user_id, email) values (1, 'user-1', 'foo@bar')` [✔]+ * `insert into emails (id, user_id, email) values (1, 'user-1', $1)` [✔]+ * `insert into emails (id, user_id, email) values (1, $2, $1)` [✔]+ * `insert into emails (id, user_id, email) values (inline(i), inline(uid), inline_param(e))` [✔]+ * default keyword+ * `insert into emails (id, user_id, email) values (default, 'foo', 'bar')` [✔]+ * `insert into emails (id, user_id, email) values (deFault, 'foo', 'bar')` [✔]+ * `insert into emails (id, user_id, email) values (DEFAULT, 'foo', 'bar')` [✔]+ * null keyword+ * `insert into emails (id, user_id, email) values (DEFAULT, 'foo', null)` [✔]+ * `insert into emails (id, user_id, email) values (DEFAULT, 'foo', NULL)` [✔]+ * `insert into emails (id, user_id, email) values (DEFAULT, 'foo', NuLL)` [✔]+ * insert ... select ...+ * `insert into emails select id, user_id, email from emails where id = 1` [✔]+ * `insert into emails select id, user_id, email from emails where id = $1` [✔]+ * `insert into users_copy select id, name, bio from users where users.id = 'uid1'` [✔]+ * returning clause+ * `insert into emails (id, user_id, email) values (1, 'user-1', 'foo@bar') returning id` [✔]+ * `insert into emails (id, user_id, email) values (1, 'user-1', 'foo@bar') returning *` [✔]+* deletes+ * `delete from users where true` [✔]+ * `delete from emails where id = 1` [✔]+ * `delete from emails where email = inline(e)` [✔]+ * `delete from users where id = 'some-id' returning id` [✔]+* updates+ * `update users set name = 'new name' where id = 'some-id'` [✔]+ * `update users set name = 'new name', bio = 'new bio' where id = 'some-id'` [✔]+ * `update users set name = inline(n) where id = 'some-id'` [✔]+ * `update users set name = 'new name' where id = 'some-id' returning id` [✔]+* scalar expressions+ * `select users.id != 'no-such-user' as neq from users` [✔]+ * `select * from users where users.id <> 'no-such-user'` [✔]+ * `select * from emails where emails.id > 0` [✔]+ * `select * from emails where emails.id >= 0` [✔]+ * `select * from emails where emails.id < 10` [✔]+ * `select * from emails where emails.id <= 10` [✔]+ * `select emails.id + 1 as plus_one from emails` [✔]+ * `select emails.id - 1 as minus_one from emails` [✔]+ * `select emails.id * 2 as times_two from emails` [✔]+ * `select * from users where users.id = 'a' and users.name = 'b'` [✔]+ * `select * from users where users.id = 'a' or users.name = 'b'` [✔]+ * `select * from users where users.name like 'A%'` [✔]+ * `select * from users where users.name ilike 'a%'` [✔]+ * `select * from users where not (users.name = 'no-one')` [✔]+ * `select -emails.id as neg_id from emails` [✔]+ * `select * from users where users.bio is null` [✔]+ * `select * from users where users.bio is not null` [✔]+ * function calls+ * `select coalesce(users.bio, 'no bio') as bio from users` [✔]+ * `select lower(users.name) as lower_name from users` [✔]+ * `select char_length(users.name) as name_len from users` [✔]+ * `select character_length(users.name) as name_len_alias from users` [✔]+ * `select "upper"(users.name) as upper_name from users` [✔]+ * `select now() as current_time` [✔]+ * `select current_date as today` [✔]+ * haskell variables in expressions+ * `select * from users where name = inline("haskellVariable")` [✔]+ * `select (emails.id + 1) * 2 as calc from emails` [✔]+ * `select * from users where users.name in ('Alice', 'Bob')` [✔]+ * `select * from users where users.name not in ('Alice', 'Bob')` [✔]+ * `select * from emails where emails.id between 0 and 10` [✔]+ * `select * from emails where emails.id not between 0 and 10` [✔]+ * `select (e.id :: text) as casted_id from emails as e` [✔]+ * `select * from users for update` [✔]+ * `select * from jsonb_test` [✔]+ * `select * from json_test` [✔]+ * `select distinct name from users` [✔]+ * `select distinct * from users` [✔]+ * `select distinct on (employee_id) employee_id, name from users` [✔]+ * `select distinct on (employee_id, name) employee_id, name, id from users` [✔]+ * order by+ * `select * from users order by name` [✔]+ * `select * from users order by name asc` [✔]+ * `select * from users order by name desc` [✔]+ * having clause+ * `select employee_id, count(id) from users group by employee_id having count(id) > 1` [✔]
squeal-postgresql-qq.cabal view
@@ -1,6 +1,6 @@ cabal-version: 3.0 name: squeal-postgresql-qq-version: 0.1.1.0+version: 0.1.1.1 synopsis: QuasiQuoter transforming raw sql into Squeal expressions. -- description: homepage: https://github.com/owensmurray/squeal-postgresql-qq@@ -8,12 +8,13 @@ license-file: LICENSE author: Rick Owens maintainer: rick@owensmurray.com-copyright: 2022 Rick Owens+copyright: 2025 Rick Owens -- category: build-type: Simple extra-source-files: README.md LICENSE+ changelog.md common dependencies build-depends:
test/test.hs view
@@ -1337,8 +1337,8 @@ ) ) )- mkStatement someName =- [ssql| select * from users where name = inline("someName") |]+ mkStatement haskellVariable =+ [ssql| select * from users where name = inline("haskellVariable") |] squealRendering1 :: Text squealRendering1 = "SELECT * FROM \"users\" AS \"users\" WHERE (\"name\" = (E'Alice' :: text))"@@ -1373,6 +1373,21 @@ squealRendering :: Text squealRendering = "SELECT * FROM \"users\" AS \"users\" WHERE \"users\".\"name\" IN ((E'Alice' :: text), (E'Bob' :: text))"+ checkStatement squealRendering stmt++ it "select * from users where users.id in (select emails.user_id from emails)" $ do+ let+ stmt+ :: Statement+ DB+ ()+ ( Field "id" Text+ , (Field "name" Text, (Field "employee_id" UUID, (Field "bio" (Maybe Text), ())))+ )+ stmt = [ssql| select * from users where users.id in (select emails.user_id from emails) |]+ squealRendering :: Text+ squealRendering =+ "SELECT * FROM \"users\" AS \"users\" WHERE (\"users\".\"id\" = ANY (SELECT \"emails\".\"user_id\" AS \"user_id\" FROM \"emails\" AS \"emails\"))" checkStatement squealRendering stmt it "select * from users where users.name not in ('Alice', 'Bob')" $ do