Where can I find more examples or actual usage?

[magthe]

Is there some place where I can find some more complex examples on table setup?

Things I'm not finding examples for, and struggle figuring out from the reference documentation alone, are

• defaults on columns that aren't serial
• defaults based on functions, e.g. gen_random_uuid()
• using check

[echatav]

Thanks to @gasi there’s a fairly complex public example of table setup and migration for ZoomHub

[echatav]

Here's an example for a table setup with a default uuid primary key and a check constraint spanning more than one column.

>>> import Squeal.PostgreSQL
>>> import Squeal.PostgreSQL.UUID.OSSP
>>> :{
type Schema = Public '[
  "unit_circle" ::: 'Table (Constraints :=> Columns)]
type Columns = '[
  "id" ::: 'Def :=> 'NotNull 'PGuuid,
  "x" ::: 'NoDef :=> 'NotNull 'PGfloat8,
  "y" ::: 'NoDef :=> 'NotNull 'PGfloat8]
type Constraints = '[
  "pk_unit_circle" ::: 'PrimaryKey '["id"],
  "length_1" ::: 'Check '["x", "y"]]
:}

>>> :{
let
  definition :: Definition (Public '[]) Schema
  definition =
    createUuidOssp >>>
    createTable #unit_circle
    ( (uuid & notNullable & default_ uuidGenerateV4) `as` #id :*
      (float8 & notNullable) `as` #x :*
      (float8 & notNullable) `as` #y )
    ( primaryKey #id `as` #pk_unit_circle :*
      check (#x :* #y) (#x * #x + #y * #y .== 1) `as` #length_1
    )
:}

[echatav]

Looks like Squeal is missing this function which wouldn't need the uuid-ossp extension and is apparently a bit faster:

genRandomUuid :: Expr (null 'PGuuid)
genRandomUuid = UnsafeExpression "gen_random_uuid()"

^ Just add this to use gen_random_uuid() as your default value.

[magthe]

Yes, I worked that one out by looking at the code for now actually. Very nice!

[magthe]

I followed the same pattern to add a default for "empty JSONB object", i.e.

emptyJsonbObject :: Expr (null 'PGjsonb)
emptyJsonbObject = UnsafeExpression "'{}'::jsonb"

Is there better way to do that? (I had hoped that aeson's Value type could be put in as default, but that didn't seem to work.)

[echatav]

I might use inline for that like inline (Jsonb emptyObject)

[magthe]

The ZoomHub link did give me several good examples and pointers, but there is one construct I can't figure out how to write: using in with a sub-query. Something like this.

select id, ...
from ...
where id in (select ...)

[echatav]

Squeal's IN is restricted to lists. For subqueries you'd use the equivalent = ANY subquery operation. Squeal has both in the Expression.Subquery module. Squeal has in_ and more helpfully here subAny. So it would be something like:

select_ (#id :* ...) (from (...) & where_ (subAny #id (.==) (select_ ...)))