Syntax error near CASE on postgres function

Question

CREATE OR REPLACE FUNCTION public.defaultmapviewport(hasura_session json)
 RETURNS viewport_info
 STABLE
AS $function$
    case hasura_session ->> 'x-hasura-default-country-code'
        when 'FR' then select lat, lng from places where slug_en = 'city-paris' 
        when 'ES' then select lat, lng from places where slug_en = 'municipality-madrid'
    end;
END $function$ language SQL;

I'm getting a syntax error near case, even though it seems right. What could be the problem?

Viewport info:

create table viewport_info(
    lat float,
    lng float
);

CASE cannot exist on its own, it needs to be part of larger query e.eg. SELECT CASE .... In other words you inverted the statement. See CASE. — Adrian Klaver
– Adrian Klaver, Commented Dec 29, 2021 at 19:59

Bergi · Accepted Answer · 2021-12-29 20:33:57Z

2

As @AdrianKlaver commented, a CASE expression can't stand on its own, it must be part of a SELECT query. You'll want to use

CREATE OR REPLACE FUNCTION public.defaultmapviewport(hasura_session json)
RETURNS viewport_info
STABLE
AS $function$
  SELECT lat, lng
  FROM places
  WHERE slug_en = (CASE hasura_session ->> 'x-hasura-default-country-code'
    WHEN 'FR' THEN 'city-paris' 
    WHEN 'ES' THEN 'municipality-madrid'
  END);
$function$ language SQL;

^{(online demo)}

edited Dec 29, 2021 at 20:33

answered Dec 29, 2021 at 20:03

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PlayMa256 Over a year ago

i'm getting a return type mismatch in function declared to return viewport_info, I'll update question with the viewport_info create statement

Bergi Over a year ago

@PlayMa256 Ah, there was a stray END at the end that I copied from the question. Fixed!

Collectives™ on Stack Overflow

Syntax error near CASE on postgres function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related