Support for composite types.

[AlexanderVanDamme]

At first i had just a jsonb for these preferred positions, but since this was untyped i decided to create a composite type for them:

CREATE TYPE preferred_position_type AS (
    position player_position,
    preference preference_type
);

this player_position and preference type is both an enum

CREATE TYPE player_position AS ENUM (
    'GK', 'RB', 'CB', 'LB', 'LWB', 'RWB', 'CDM', 'CM', 'CAM', 
    'RM', 'LM', 'RW', 'LW', 'CF', 'ST'
);

CREATE TYPE preference_type AS ENUM (
    'preferred', 'filler', 'veto'
);

ALTER TABLE players ADD COLUMN preferred_positions preferred_position_type[] DEFAULT '{}';

But when i run it i get the following error

gleam run -m squirrel
Compiled in 0.03s
Running squirrel.main
Error: Unsupported type

 ╭─ ./src/squirrels/sql/get_all_players.sql

One of the rows returned by this query has type
preferred_position_type which I cannot currently generate code
for.

If you would like for this type to be supported, please open an
issue at
https://github.com/giacomocavalieri/squirrel/issues/new

🥜 I could still generate 4 queries.

Would be nice if such typing would be supported, would cleanup the decoders needed for it !
(or maybe there's other ways to type such data?)

[ninanomenon]

I would love to see this! I am running in the same problem right now.

[giacomocavalieri]

Hello! I'm not sure this can be done right now in Postgres, I'll need to have a look at how those types are represented internally by Postgres and if it stores enough informations we can access to generate proper types from it. If I recall correctly what blocked me was that Postgres used a generic "record" type or something like that for those custom types

[giacomocavalieri]

Small update: looking into it, there might be a way to actually generate code for custom types! The only downside is that all fields of a custom type are always nullable so the generated code would look like this:

pub type PreferredPositionType {
  PreferredPositionType(position: Option(PlayerPosition), preference: Option(PreferenceType))
}

It's not the best, but Postgres does not allow adding a not null constraint to the fields of custom types

[ninanomenon]

That's pretty cool! 😍

[lpil]

@ninanomenon (lmao your name still breaking github UI, love it) could you share a bit about your use case please 🙏

@AlexanderVanDamme In this case you may want to use a table for the preferred_positions rather than an array of rows inside a table. This is generally considered best practice with RDBMS, and here specifically it means that you won't have to deal with the position and preference values being wrapped in Option, as tables support non-nullable values while custom types do not.

[ninanomenon]

We are using enums a lot in different systems. For example to store the state of connected creditcard terminals. Just strings would work too but these states are pretty defined over the entire platform.

(Braking UIs is my hobby ✨)

[lpil]

Sorry, not the enum bit, the composite types bit!

[giacomocavalieri]

I'm making this a discussion so we can hear from more folks and learn about other use cases before rushing in an implementation! Thanks all!