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
  • Save fponticelli/7c4da250dca6c0a50e523d13adac97e6 to your computer and use it in GitHub Desktop.
Save fponticelli/7c4da250dca6c0a50e523d13adac97e6 to your computer and use it in GitHub Desktop.
Create a sum type for colors in sql
create type custom_color as (r smallint, g smallint, b smallint);
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 $$
begin
return
(c.named IS NOT NULL)::int +
(c.custom IS NOT NULL)::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(null, '(250,7,255)')); -- success
insert into color_tester (color) values(row('green', null)); -- success
insert into color_tester (color) values(row('red', null)); -- success
-- insert into color_tester (color) values(row(null, null)); -- failure
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment