Default values for function parameters

Question

Am trying to add a date string onto this function so instead of getting ALL records I am looking to only get the last 7 days.

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$
;

At present if I call the function

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

The function works as expected. However, what I am trying to achieve is the _startPeriod and _endPeriod to either be a default of 30 days, with _startPeriod being the earlier date (30 days from today) and _endPeriod being today (current_date or now() for example).

I have tried declaring a _startperiod and _endperiod like in the below.

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$
;

Am trying to default it so the startperiod is 30 days ago and the end period is today, but when I go to run the new function.

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

I get:

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

Is there a better approach to this?

Ideally what I would like is for the startPeriod and endPeriod to allow inputs when calling the function but if no inputs are added in then to default to the last 30 days.

Answer 1

Postgres functions can simply define default values for input parameters:

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$;

fiddle

Note how I placed parameters with default at the end of the list. This way you can call the function with arguments for leading parameters and simply skip the rest to assume defaults.

See:

• Optional argument in PL/pgSQL function
• Add function parameter to SQL query WHERE clause
• The forgotten assignment operator "=" and the commonplace ":="

(There was a lot of noise & nonsense in your function which I removed. Like, the two CTEs in your query did a whole lot of nothing ...)

The way you pass _sort_field_and_direction makes me uncomfortable as that's wide open to SQL injection. I wouldn't do that.
Passing parts of the ORDER BY clause is not a problem per se. But the way you do it, as indiscriminate text blob, prevents us from quoting identifiers and values (and identifying key words) properly,thereby creating a "safe" vessel for SQL injection.

All the other parameters are just values and can be passed as such. Dynamic SQL is only needed for your ugly concatenation of _sort_field_and_direction.

Related:

• Why colname as input param of function = not good idea?
• Passing ROWTYPE parameter to EXECUTE

Answer 2

I did a simplified version of your function, but I hope it will demonstrate a workable solution for you:

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$
;

Instead of trying to construct a query string that must handle null or a value, you can use COALESCE to test if the parameter is not null, then use it, otherwise SELECT CURRENT_DATE - 30.

Now, you probably need to build the string anyhow for limit etc, but you can use the above technique for your predicates.

Fiddle