Alter a function in Postgres 12 to allow for dates to be default or populated

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 the _startPeriod being the oldest date (30 days from today) and the _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.

score 2 · Answer 1 · answered Nov 17 '22 at 16:04

You can specify default arguments for a function:

CREATE OR REPLACE FUNCTION public.customerOrders(
    _customer_id integer, 
    _startperiod timestamp with time zone DEFAULT CURRENT_DATE-30, 
    _endperiod timestamp with time zone DEFAULT CURRENT_DATE,  
    _sort_field_and_direction character varying DEFAULT ‘order_id’, 
    _limit integer DEFAULT 999, 
    _offset integer DEFAULT 0, 
    OUT id integer, 
    OUT customerid integer, 
    OUT description character varying, 
    OUT placedon timestamp with time zone
)
RETURNS SETOF record
 LANGUAGE plpgsql
AS $function$
…

If you don’t have the default arguments stacked on the end, you would have to use named arguments.

Another option is to set the unknowns to NULL and use COALESCE() to convert it to a default value:

startperiod = COALESCE(_start_period, CURRENT_DATE-30);

Aside: Avoid mixing date and timestamptz types. The date to time stamp conversion is quite slow and can have surprising effects if multiple time zones are in use. Use date_trunc(‘day’, …) if you need to set to a date boundary.

Alter a function in Postgres 12 to allow for dates to be default or populated

1 Answers1