Skip to content

Instantly share code, notes, and snippets.

@fponticelli
Forked from mlms13/postgresql_color_adt.sql
Created September 19, 2017 02:32
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save fponticelli/ae4508bcdfcda0931156cba154e47f31 to your computer and use it in GitHub Desktop.
Save fponticelli/ae4508bcdfcda0931156cba154e47f31 to your computer and use it in GitHub Desktop.
Create a sum type for colors in sql
-- data NamedColor = Red | Green | Blue
-- data CustomColor = CustomColor
-- { r :: Int
-- , g :: Int
-- , b :: Int
-- }
-- data Color = NamedColor | CustomColor
create type custom_color as (r int, g int, b int);
create type named_color as enum ('red', 'green', 'blue');
create type color as (named named_color, custom custom_color);
create or replace function check_color(c color) returns boolean as $$
declare
custom_int integer;
named_int integer;
begin
select case when c.named is null then 0 else 1 end into named_int;
select case when c.custom is null then 0 else 1 end into custom_int;
return custom_int + named_int = 1;
end;
$$ language plpgsql;
create table color_tester (
color color not null default '(red,)' CHECK(check_color(color))
);
insert into color_tester (color) values(row(null, '(0,7,255)')); -- success
insert into color_tester (color) values(row('green', '(0,7,255)')); -- failure
select * from color_tester;
select json_strip_nulls(row_to_json(color_tester)) from color_tester;
-- where color = row('red',null)::impl_color;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment