problems with function pg_catalog.btrim(date)

Started by Karina Guardadoover 16 years ago2 messagesgeneral
Jump to latest
#1Karina Guardado
karina.guardado@ues.edu.sv

Hi everybody,

I hope some one can help me, I have created the following function but
I always get the error that there is not function
pg_catalog.btrim(date), in the version postgresql 8.1 it worked but now
it does not so I don't know if it is related with a configuration
problem of the postgresql or somethin in the function code :

thanks

karina

CREATE FUNCTION edad(date) RETURNS integer
AS $_$
DECLARE
fechnac ALIAS FOR $1;
dia_nac integer;
mes_nac integer;
anho_nac integer;
fecha_nac date;
fecha_temp text;
hoy date;
dia integer;
mes integer;
anho integer;
temp integer;
edad integer;

BEGIN

hoy := current_date;
hoy := date(trim(hoy));

fecha_nac:= date(trim(fechnac));
fecha_temp:=CAST(fecha_nac AS text );

dia_nac :=CAST(substring(fecha_temp from 1 for 2) AS integer );
mes_nac := CAST (substring(fecha_temp from 4 for 2) AS integer);
anho_nac :=CAST(substring(fecha_temp from 7 for 4) AS integer );

dia :=CAST(substring(hoy from 1 for 2) AS integer );
mes := CAST (substring(hoy from 4 for 2) AS integer);
anho := CAST(substring(hoy from 7 for 4) AS integer );

edad:= (anho-anho_nac)-1;

IF mes+1-mes_nac < 0 THEN
RETURN edad;
END IF;

IF mes+1-mes_nac < 0 THEN
RETURN edad;
END IF;

IF mes+1-mes_nac > 0 THEN
RETURN edad+1;
END IF;

IF dia-dia_nac >= 0 THEN
RETURN edad+1;
ELSE
RETURN edad;
END IF;
END;

$_$
LANGUAGE plpgsql;

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Karina Guardado (#1)
Re: problems with function pg_catalog.btrim(date)

Karina Guardado <karina.guardado@ues.edu.sv> writes:

I hope some one can help me, I have created the following function but
I always get the error that there is not function
pg_catalog.btrim(date), in the version postgresql 8.1 it worked but now
it does not so I don't know if it is related with a configuration
problem of the postgresql or somethin in the function code :

What in the world do you imagine that trim() on a date would be good
for? Just get rid of the "date(trim())" lines.

I think this accidentally failed to fail pre-8.3 because there was an
implicit cast from date to text, so it would convert the date to text,
remove leading/trailing blanks (which there wouldn't be any of), and
then convert the string back to date. An expensive and pointless no-op.

This bit is going to fail too:

dia :=CAST(substring(hoy from 1 for 2) AS integer );
nmes := CAST (substring(hoy from 4 for 2) AS integer);
anho := CAST(substring(hoy from 7 for 4) AS integer );

While that sort of worked in 8.1, it was always bad coding style and
fragile as can be --- think what will happen if the user changes the
datestyle setting. Replace this with extract(day ... ) and so forth,
and you'll have code that is safer, faster, and more standard/portable.

regards, tom lane