CODEWITHSUNDEEP
postgresqlhasura
PlayMa256
PlayMa256

Reputation: 6841

Syntax error near CASE on postgres function

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
);

Upvotes: 0

Views: 458

Answers (1)

Bergi
Bergi

Reputation: 664548

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)

Upvotes: 2

Related Questions