Create a postgresql function that returns a string 'fall', 'spring' depending on year

Question

I need to create a function that returns the string 'fall' or 'spring' depending on the month of the year. If the function was named getterm and took no parameters I would like to use it in a select statement like this:

select name, classname, getterm from classtable

where classtable holds the names of the classes we offer. The result set would include the columns as follows:

-Jack, Data Systems, Sp2022

-Jill, Web Stuff, F2023

I have used the now() function. I can also use extract(quarter from now()) to get my current quarter. It would seem simple then to use an 'if' or 'case' clause to return 'spring' or 'fall' based upon the quarters. I just haven't found any examples of a function like this.

Can anyone suggest some sample code ?

Answer 1

Per documentation from here CASE:

There is a “simple” form of CASE expression that is a variant of the general form above:

CASE expression
    WHEN value THEN result
    [WHEN ...]
    [ELSE result]
END

The first expression is computed, then compared to each of the value expressions in the WHEN clauses until one is found that is equal to it. If no match is found, the result of the ELSE clause (or a null value) is returned. This is similar to the switch statement in C.

This translates in your case to:

SELECT
    CASE extract('quarter' FROM now())
    WHEN 1 THEN
        'winter'
    WHEN 2 THEN
        'spring'
    WHEN 3 THEN
        'summer'
    WHEN 4 THEN
        'fall'
    END;

 case  
--------
 spring

Answer 2

Thank you to Adrian.. I now have the working function:

create function getTermString() returns text as $$
select case extract(quarter from now()) 
when 1 then 'sp'
when 2 then 'sp'
when 3 then 'f'
when 4 then 'f'
end ;
$$ language SQL;