Month and year instead of the date variable

Question

I work with PostgreSQL and I have three variables in a function and one of them is of type date:

CREATE OR REPLACE FUNCTION public.fn_reporte_venta_mes(
IN p_fech = date,
IN p_client integer,
IN p_comprobante character

The parameter p_fech serves to enter a date from PHP in format '1111-11-11'. Instead, I want to only insert date a month and year format '1111-11'. But I do not know how to change the data type of the p_fech variable since when I put only the day and month in PHP I get compatibility error.

My complete function:

CREATE OR REPLACE FUNCTION public.fn_reporte_venta(
IN p_fech date,
IN p_client integer,
IN p_comprobante character)
RETURNS TABLE(nro integer, fecha date, tipo character varying, cliente text, porc_igv numeric, st numeric, igv numeric, total numeric) AS
$BODY$
begin
return query
select v.numero_venta,
       v.fecha_venta,
       tc.descripcion,
       concat(apellido_paterno, ' ', apellido_materno, ' ', nombres) as client,
       v.porcentaje_igv,
       v.sub_total,
       v.igv,
       v.total
from venta v 
     inner join cliente cl on v.codigo_cliente = cl.codigo_cliente
     inner join tipo_comprobante tc on v.codigo_tipo_comprobante = tc.codigo_tipo_comprobante
where v.estado = 'E' and
      (
    case when p_fech = '11-11-1111' then 
        1 = 1
    else
        v.fecha_venta = p_fech
    end
      )
      and
      (
    case when p_client = 0 then 
        1 = 1
    else
        cl.codigo_cliente = p_client
    end
      ) and 
      (
    case when p_comprobante = '00' then 
        1 = 1
    else
        tc.codigo_tipo_comprobante = p_comprobante
    end
      )
      order by 2;
     end
    $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100
   ROWS 1000;

It would help to declare your version of Postgres and the language used in the function. (SQL, plpgsql or something else?) — Erwin Brandstetter, Nov 10 '16 at 22:47
Please [edit] your question with essential information. `SELECT version()` tells you the version number of Postgres. — Erwin Brandstetter, Nov 10 '16 at 23:39

score 1 · Accepted Answer · edited May 23 '17 at 12:07

Use a character type parameter (text or varchar, not character!) and the function to_date() to convert the input to a date.

Your function, simplified and fixed:

CREATE OR REPLACE FUNCTION public.fn_reporte_venta(p_fech text
                                                 , p_client integer
                                                 , p_comprobante text)
  RETURNS TABLE(nro integer, fecha date, tipo varchar, cliente text
              , porc_igv numeric, st numeric, igv numeric, total numeric) AS
$func$
DECLARE
   v_fech date := to_date(p_fech, 'YYYY-MM-DD');  -- transform to date
BEGIN
   RETURN QUERY
   SELECT v.numero_venta,
          v.fecha_venta,
          t.descripcion,
          concat_ws(' ', c.apellido_paterno, c.apellido_materno, c.nombres) AS client,
          v.porcentaje_igv,
          v.sub_total,
          v.igv,
          v.total
   FROM   venta v 
   JOIN   cliente c USING (codigo_cliente)
   JOIN   tipo_comprobante t USING (codigo_tipo_comprobante)
   WHERE  v.estado = 'E'
   AND   (v_fech = '1111-11-11' OR v.fecha_venta = v_fech)  -- var goes here
   AND   (p_client = 0          OR c.codigo_cliente = p_client)
   AND   (p_comprobante = '00'  OR t.codigo_tipo_comprobante = p_comprobante)
   ORDER  BY 2;
END
$func$  LANGUAGE plpgsql STABLE ROWS 1000;

Always use the ISO-8601 format for date literals (YYYY-MM-DD), which is unambiguous with any locale setting. Don't fall for local syntax dialects, they break if a session should run with a different locale.

I am using a date variable in the plpgsql function, which can be assigned at declaration right away.

This expression is very versatile:

to_date(p_fech, 'YYYY-MM-DD');

to_date() allows to omit elements to the right to 1 for missing data. All of these are valid and result in '2016-01-01':

SELECT to_date('2016-01-01', 'YYYY-MM-DD') 
     , to_date('2016-1-1'  , 'YYYY-MM-DD') 
     , to_date('2016-01'   , 'YYYY-MM-DD') 
     , to_date('2016-'     , 'YYYY-MM-DD') 
     , to_date('2016'      , 'YYYY-MM-DD');

So you can pass in full dates or truncated to month or even year. You always specify a single day this way.

Or to always cover a whole month:

...
   v_fech date := to_date(p_fech, 'YYYY-MM');  -- no "-DD" truncates to month
...
   AND   (v_fech = '1111-11-01'  -- also truncated to 1st of month!
       OR v.fecha_venta >= v_fech
      AND v.fecha_venta <  v_fech + interval '1 month')
...

Why not data type character? Because that's an outdated, largely useless type, typically a misunderstanding:

Also note how I replaced your verbose CASE statements with simple OR expressions.

And some other minor improvements ...

The expression is fine: `v_fech date := to_date(p_fech, 'YYYY-MM-DD')` to allow foll dates specifying a single day as well. Consider the explanation in my answer. You need the *exact* date (first of the month!) in `v.fecha_venta`, though. I added a variant to always include all days of the month above. — Erwin Brandstetter, Nov 11 '16 at 01:56
If I make a select with the complete date it works perfectly but if I make a select with the month and the year it does not show anything select * from fn_reporte_venta ( '2016-05', 2, '01') — Android Facil, Nov 11 '16 at 02:02

Month and year instead of the date variable

1 Answers1