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

Question

This question already has answers here:

Default values for function parameters (2 answers)

Closed 2 years ago.

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.

Answer 1

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.

• rdbmsNoob: altering the arguments alone has virtually done what is required. I placed the _startperiod timestamp with time zone DEFAULT CURRENT_DATE-30, _endperiod timestamp with time zone DEFAULT CURRENT_DATE at the end of the function and ran the function without dates (so didnt call the _start and endPeriod) and the function does as expected! I also ran the function again this time calling a start and endPeriod and again does what is expected!. Is there anyway however of calling the function but instead of completely leaving out is to put null for both?
• user234725: You will have to add the COALESCE() I referenced in my answer to convert the NULL. You can do this in the DEFAULT declaration, I believe. Not somewhere I can test it at the moment.