CREATE OR REPLACE FUNCTION signo(mi_fecha date)
RETURNS varchar
AS $$
DECLARE
mi_signo varchar;
BEGIN
WITH zodiaco AS (
SELECT
(EXTRACT(YEAR FROM NOW())-1 || '-' || 12 || '-' || 22)::date fecha_min,
(EXTRACT(YEAR FROM NOW()) || '-' || 1 || '-' || 19)::date fecha_max,
'Capricornio' signo
UNION ALL
SELECT
(EXTRACT(YEAR FROM NOW()) || '-' || 1 || '-' || 20)::date fecha_min,
(EXTRACT(YEAR FROM NOW()) || '-' || 2 || '-' || 19)::date fecha_max,
'Acuario' signo
UNION ALL
SELECT
(EXTRACT(YEAR FROM NOW()) || '-' || 2 || '-' || 20)::date fecha_min,
(EXTRACT(YEAR FROM NOW()) || '-' || 3 || '-' || 20)::date fecha_max,
'Piscis' signo
UNION ALL
SELECT
(EXTRACT(YEAR FROM NOW()) || '-' || 3 || '-' || 21)::date fecha_min,
(EXTRACT(YEAR FROM NOW()) || '-' || 4 || '-' || 20)::date fecha_max,
'Aries' signo
UNION ALL
SELECT
(EXTRACT(YEAR FROM NOW()) || '-' || 4 || '-' || 21)::date fecha_min,
(EXTRACT(YEAR FROM NOW()) || '-' || 5 || '-' || 20)::date fecha_max,
'Tauro' signo
UNION ALL
SELECT
(EXTRACT(YEAR FROM NOW()) || '-' || 5 || '-' || 21)::date fecha_min,
(EXTRACT(YEAR FROM NOW()) || '-' || 6 || '-' || 21)::date fecha_max,
'Geminis' signo
UNION ALL
SELECT
(EXTRACT(YEAR FROM NOW()) || '-' || 6 || '-' || 22)::date fecha_min,
(EXTRACT(YEAR FROM NOW()) || '-' || 7 || '-' || 22)::date fecha_max,
'Gancer' signo
UNION ALL
SELECT
(EXTRACT(YEAR FROM NOW()) || '-' || 7 || '-' || 23)::date fecha_min,
(EXTRACT(YEAR FROM NOW()) || '-' || 8 || '-' || 23)::date fecha_max,
'Leo' signo
UNION ALL
SELECT
(EXTRACT(YEAR FROM NOW()) || '-' || 8 || '-' || 24)::date fecha_min,
(EXTRACT(YEAR FROM NOW()) || '-' || 9 || '-' || 23)::date fecha_max,
'Virgo' signo
UNION ALL
SELECT
(EXTRACT(YEAR FROM NOW()) || '-' || 9 || '-' || 24)::date fecha_min,
(EXTRACT(YEAR FROM NOW()) || '-' || 10 || '-' || 22)::date fecha_max,
'Libra' signo
UNION ALL
SELECT
(EXTRACT(YEAR FROM NOW()) || '-' || 10 || '-' || 23)::date fecha_min,
(EXTRACT(YEAR FROM NOW()) || '-' || 11 || '-' || 22)::date fecha_max,
'Escorpio' signo
UNION ALL
SELECT
(EXTRACT(YEAR FROM NOW()) || '-' || 11 || '-' || 23)::date fecha_min,
(EXTRACT(YEAR FROM NOW()) || '-' || 12 || '-' || 21)::date fecha_max,
'Sagitario' signo
UNION ALL
SELECT
(EXTRACT(YEAR FROM NOW()) || '-' || 12 || '-' || 22)::date fecha_min,
(EXTRACT(YEAR FROM NOW())+1 || '-' || 1 || '-' || 19)::date fecha_max,
'Capricornio' signo
)
SELECT signo INTO mi_signo FROM zodiaco WHERE mi_fecha BETWEEN fecha_min AND fecha_max;
RETURN mi_signo;
END $$
LANGUAGE plpgsql;
SELECT signo(now()::date);
