Return the name of the month as select

PostgreSQL has built-in methods for translating time strings, so it is unnecessary to create custom methods. You can use the to_char function, passing the extracted date as the first parameter, as the second parameter, the string 'Month' .

This will return the name of the month with the first letter capitalized (there is also 'MONTH' and 'month', if you prefer), according to the parameter lc_time defined in its postgresql.conf . For my case, where lc_time is C :

postgres=# select to_char(current_date, 'Month');
  to_char
-----------
 March
(1 row)

If you want the name of the month in another language without having to change the .conf, you can set lc_time to its own session and use the string 'TMMonth' . "TM" stands for "Translation Mode":

postgres=# set lc_time  TO 'pt_BR.UTF-8';
SET
postgres=# select to_char(current_date, 'TMMonth');
 to_char
---------
 Março
(1 row)

postgres=# set lc_time  TO 'fr_FR.UTF-8';
SET
postgres=# select to_char(current_date, 'TMMonth');
 to_char
---------
 Mars
(1 row)

postgres=# set lc_time  TO 'ja_JP.UTF-8';
SET
postgres=# select to_char(current_date, 'TMMonth');
 to_char
---------
 3月
(1 row)

So, complementing the initial query of your question, try with:

select to_char(extract('Month',data), 'Month');

Or, if your PostgreSQL is not in Portuguese, use:

set lc_time to 'pt_BR.UTF-8';
select to_char(extract('Month',data), 'TMMonth');

I use the following function:

CREATE OR REPLACE FUNCTION public.namemonth (
  integer
)
RETURNS varchar AS
$body$
SELECT Case $1
when 1 then 'Janeiro'
when 2 then 'Fevereiro'
when 3 then 'Março'
when 4 then 'Abril'
when 5 then 'Maio'
when 6 then 'Junho'
when 7 then 'Julho'
when 8 then 'Agosto'
when 9 then 'Setembro'
when 10 then 'Outubro'
when 11 then 'Novembro'
when 12 then 'Dezembro'
else NULL
end
$body$
LANGUAGE 'sql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

To use:

Select namemonth(extract('Month',data))

Here's another alternative:

SELECT
   (ARRAY[
          'Janeiro',
          'Fevereiro',
          'Março',
          'Abril',
          'Maio',
          'Junho',
          'Julho',
          'Agosto',
          'Setembro',
          'Outubro',
          'Novembro',
          'Dezembro'])[ EXTRACT(MONTH FROM DATE '01.01.2018') ];