月份和年份，而不是日期变量

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服务于进入从PHP日期格式'1111-11-11'。相反，我只想插入日期格式为“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），这是明确的任何区域设置。不要使用本地语法方言，如果会话应该以不同的语言环境运行，它们会中断。

我在plpgsql函数中使用date变量，它可以在声明中立即分配。

这种表达是非常通用的：

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

to_date()允许省略元素到右侧，以1对丢失数据。所有这些都是有效的，并导致“2016年1月1日”：

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?

另外请注意我如何更换您的详细CASE语句简单或表达式。

和其他一些小改进...