postgresql - 月份和年份而不是日期变量

Question

我使用 PostgreSQL，我在一个函数中有三个变量，其中一个是 date 类型:

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

参数 p_fech 用于以格式“1111-11-11”从 PHP 输入日期。相反，我只想插入日期、月份和年份格式“1111-11”。但是我不知道如何更改 p_fech 变量的数据类型，因为当我在 PHP 中只输入日期和月份时，我会遇到兼容性错误。

我的完整功能:

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;

Answer 1

使用字符类型参数(text 或 varchar，不是 character!)和函数 to_date() 来将输入转换为日期。

您的功能，经过简化和修复:

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;

始终对日期文字使用 ISO-8601 格式(YYYY-MM-DD)，这对于任何语言环境设置都是明确的。不要迷恋本地语法方言，如果 session 应该以不同的语言环境运行，它们就会中断。

我在 plpgsql 函数中使用了一个 date 变量，它可以在声明时立即赋值。

这个表达式非常通用:

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

to_date() 允许省略右边的元素到 1 以表示丢失的数据。所有这些都是有效的并导致 '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');

因此您可以传递完整日期或截断为月份甚至年份。您始终可以通过这种方式指定一天。

或者总是涵盖整个月:

...
   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')
...

为什么数据类型不是 character？因为这是一种过时的、基本上无用的类型，通常是一种误解:

• Best way to check for "empty or null value"

• Any downsides of using data type "text" for storing strings?

另请注意我是如何用简单的 OR 表达式替换冗长的 CASE 语句的。

还有一些其他的小改进......