PostgreSQL greener: syntax error at or near "extract"-CodePudding

I am a greener on writing PostgreSQL. I wrote a simple function and it yielded error. Can anyone tell me what's wrong with it? Thanks a lot!

create or replace function constellation(birthday date)
returns varchar(8)
begin atomic
return (if extract(month from birthday)=12 then '摩羯')
end

ERROR:  syntax error at or near "extract"
LINE 4: return (if extract(month from birthday)=12 then '摩羯')
                   ^
SQL state: 42601
Character: 100

===== Updates: I've also tried case method, and it still raied error:

create or replace function constellation(birthday date)
returns varchar(8)
begin atomic
return (case when extract(month from birthday)=12 then '摩羯')
end

ERROR:  syntax error at or near ")"
LINE 4: ...turn (case when extract(month from birthday)=12 then '摩羯')
                                                                      ^
SQL state: 42601
Character: 148

CodePudding user response：

You are writing a function with a SQL language body. SQL has no IF statement, so you need to use a SELECT statement that uses a CASE expression:

create or replace function constellation(birthday date)
returns varchar(8)
begin atomic
 select case 
          when extract(month from birthday) = 12 
               and extract(day from birthday)<=21 then '摩羯'
          when extract(month from birthday) = 11 then 'eleven'
          when extract(month from birthday) = 10 then 'ten'
          else 'No idea'
        end;
end;

CodePudding user response：

Postgres does not specifically require a from clause on select, but even without it you can still include a where clause (unusual but doable). This permits reducing to a single statement and without the case expression by putting the relational operators in the where clause. Also, as only a SQL it reduces to a sql function: (see demo)

create or replace function constellation(birthdate date)
  returns varchar
  language sql 
as $$
  select '摩羯'
   where extract(month from birthdate) = 12 
     and extract(month from birthdate) <= 21 
$$;