Skip to content

Instantly share code, notes, and snippets.

@beargiles
Last active October 25, 2021 23:35
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 beargiles/a701182839b35c0f2293e6ab5412cac8 to your computer and use it in GitHub Desktop.
Save beargiles/a701182839b35c0f2293e6ab5412cac8 to your computer and use it in GitHub Desktop.
PostgreSQL function using user-defined enum type in case statement
CREATE OR REPLACE FUNCTION get_gluon(color color) RETURNS TEXT AS $$
#print_strict_params on
DECLARE
duck TEXT;
BEGIN
CASE color
WHEN 'RED'::color THEN
duck := 'Huey';
WHEN 'GREEN'::color THEN
duck := 'Dewey';
WHEN 'BLUE'::color THEN
duck := 'Louis';
ELSE
RAISE EXCEPTION 'unknown color %s', color;
END CASE;
return duck;
END;
$$ LANGUAGE plpgsql
IMMUTABLE
RETURNS NULL ON NULL INPUT
PARALLEL SAFE;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment