主页 / dba / 问题 / 319529
Accepted
rdbmsNoob
Asked: 2022-11-12 07:59:00 +0800 CST

函数参数的默认值

  • 772

我正在尝试在此函数上添加一个日期字符串，因此我希望只获取最近 7 天的所有记录，而不是获取所有记录。

CREATE OR REPLACE FUNCTION public.customerOrders(_customer_id integer, _startperiod timestamp with time zone, _endperiod timestamp with time zone,  _sort_field_and_direction character varying, _limit integer, _offset integer, OUT id integer, OUT customerid integer, OUT description character varying, OUT placedon timestamp with time zone)
 RETURNS SETOF record
 LANGUAGE plpgsql
AS $function$

DECLARE
    f_string            TEXT;
    f_max_rows          INTEGER := 100;

BEGIN
  f_string := '';
  f_string := 'WITH
               limited_orders AS (SELECT * FROM customerorder
                                 WHERE customer_id =  ' || _customer_id || '
                                 ORDER BY order_id DESC
                                 LIMIT ' || f_max_rows || '
                               ),
      orders AS(
      SELECT order_id, customer_id, order_placed_on,order_description
      FROM limited_orders
      WHERE customer_id = ' || _customer_id || '
        GROUP BY order_id, customer_id,order_placed_on,order_description 
    )
    SELECT order_id as id, customer_id as customerId, order_description as description, order_placed_on as placedOn   
FROM customerorder
    where 
(order_placed_on >= ''%s'' AND order_placed_on <= ''%s'')
    ORDER BY ' || _sort_field_and_direction || ' LIMIT ' || _limit || ' OFFSET ' || _offset;

  raise notice '%', f_string;
RETURN QUERY
EXECUTE FORMAT(f_string, _startperiod, _endperiod);
       END;
$function$
;

目前如果我调用函数

SELECT * FROM public.customerOrders('2579927','2022-10-01'::date,'2022-10-05'::date,'placedOn DESC','50','0')

该功能按预期工作。但是，我想要实现的是_startPeriod和_endPeriod要么是 30 天的默认值，要么是_startPeriod较早的日期（从今天起 30 天），要么是今天（例如_endPeriodcurrent_date 或）。now()

我试过在下面声明 a_startperiod和_endperiodlike 。

CREATE OR REPLACE FUNCTION public.customerOrders1(_customer_id integer,  _sort_field_and_direction character varying, _limit integer, _offset integer, OUT id integer, OUT customerid integer, OUT description character varying, OUT placedon timestamp with time zone)
 RETURNS SETOF record
 LANGUAGE plpgsql
AS $function$

DECLARE
    f_string            TEXT;
    f_max_rows          INTEGER := 100;
    _startPeriod        DATE;
    _endPeriod          DATE;

begin
  _startperiod := 'select current_date - 30';
  _endPeriod := 'select current_date';
  f_string := '';
  f_string := 'WITH
               limited_orders AS (SELECT * FROM customerorder
                                 WHERE customer_id =  ' || _customer_id || '
                                 ORDER BY order_id DESC
                                 LIMIT ' || f_max_rows || '
                               ),
      orders AS(
      SELECT order_id, customer_id, order_placed_on,order_description
      FROM limited_orders
      WHERE customer_id = ' || _customer_id || '
        GROUP BY order_id, customer_id,order_placed_on,order_description 
    )
    SELECT order_id as id, customer_id as customerId, order_description as description, order_placed_on as placedOn   
FROM customerorder
    where 
(order_placed_on >= ''%s'' AND order_placed_on <= ''%s'')
    ORDER BY ' || _sort_field_and_direction || ' LIMIT ' || _limit || ' OFFSET ' || _offset;

  raise notice '%', f_string;
RETURN QUERY
EXECUTE FORMAT(f_string, _startperiod, _endperiod);
       END;
$function$
;

我试图默认它，所以开始时间是 30 天前，结束时间是今天，但是当我去运行新功能时。

SELECT * FROM public.customerOrders1('2579927','placedOn DESC','50','0');

我得到：

ERROR: invalid input syntax for type date: "select current_date - 30"

有更好的方法吗？

理想情况下，我希望 startPeriod 和 endPeriod 在调用函数时允许输入，但如果没有添加输入，则默认为最近 30 天。

postgresql

2 个回答

  1. Best Answer

    Postgres 函数可以简单地定义输入参数的默认值：

    CREATE OR REPLACE FUNCTION public.customer_orders(
        _customer_id int
      , _sort_field_and_direction text
      , _limit int
      , _offset int
      , _start_period date = CURRENT_DATE - 30  -- !!!
      , _end_period   date = CURRENT_DATE       -- !!!
      )
  RETURNS TABLE (id int, customerid int, description varchar, placedon timestamptz)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sql  text := '
   SELECT order_id, customer_id, order_description, order_placed_on
   FROM   customerorder
   WHERE  customer_id = $1
   AND    order_placed_on BETWEEN $2 AND $3
   ORDER  BY ' || _sort_field_and_direction  -- open to SQL injection !!!
   || '
   LIMIT  $4
   OFFSET $5';
BEGIN
   RAISE NOTICE '%', _sql;

   RETURN QUERY EXECUTE _sql
   USING _customer_id   -- $1
       , _start_period  -- $2
       , _end_period    -- $3
       , _limit         -- $4
       , _offset        -- $5
   ;
END
$func$;

    小提琴

    请注意我是如何将默认参数放在列表末尾的。通过这种方式，您可以使用前导参数的参数调用函数，并简单地跳过其余​​部分以采用默认值。

    看：

    （我删除了你的函数中有很多噪音和废话。比如，你查询中的两个 CTE 什么也没做......）

    你通过的方式_sort_field_and_direction让我感到不舒服，因为它对SQL 注入是开放的。我不会那样做。
    通过ORDER BY条款的部分本身不是问题。但是你这样做的方式，作为不分青红皂白的文本 blob，阻止我们正确引用标识符和值（以及识别关键字），从而为 SQL 注入创建一个“安全”容器。

    所有其他参数都只是值，可以这样传递。动态 SQL 只需要丑陋的_sort_field_and_direction.

    有关的：

    • 2

  2. 我做了你的功能的简化版本，但我希望它能为你展示一个可行的解决方案：

    CREATE OR REPLACE FUNCTION public.customerOrders
  (_customer_id integer
  , _startperiod timestamp with time zone
  , _endperiod timestamp with time zone
  ,  _sort_field_and_direction character varying
  , _limit integer
  , _offset integer
  , OUT id integer
  , OUT customerid integer
  , OUT description character varying
  , OUT placedon timestamp with time zone)
 RETURNS SETOF record
 LANGUAGE plpgsql
AS $function$
DECLARE
    f_string            TEXT;
    f_max_rows          INTEGER := 100;
BEGIN
  RETURN QUERY 
    SELECT order_id, customer_id, order_description, order_placed_on 
    FROM public.customerorder
    WHERE order_placed_on >= COALESCE(_startperiod, (select current_date - 30));
END;
$function$
;

    您可以使用 COALESCE 来测试参数是否不为空，然后使用它，而不是尝试构造一个必须处理 null 或值的查询字符串，否则使用它，否则选择 CURRENT_DATE - 30。

    现在，您可能需要为 limit 等构建字符串，但您可以将上述技术用于谓词。

    小提琴

    • 1

相关问题