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.

• Erwin Brandstetter: "... it has issues doing so." Please be specific. The function you present does not work at all. Please provide a minimal, working function to work with.
• rdbmsNoob: @ErwinBrandstetter have edited the post to make more sense....the issue is I can get the function to run just fine by inputting dates but would like the dates to be either inputted by the end user/app or a default of 30 days. Have got the code to run when inputting the dates but struggling to make the dates default. As in the last 30 days off the order_placed_on column.
• Lennart - Slava Ukraini: Is this what you are trying to do ... order_placed_on >= (select current_date - 30) ... ?
• Lennart - Slava Ukraini: Your current code does something like ...order_placed_on >= 'select current_date - 30'...
• rdbmsNoob: @Lennart-SlavaUkraini that would also work. However I am trying to code it so that if a startPeriod (which would be the oldest date e.g. 30 days) and the endPeriod which would be today are not inputted by the end user it would simply default to 30 days, so mainly the startPeriod would be IF NULL then 30 days, is this possible?

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

• rdbmsNoob: Thanks for this @erwin-brandstetter this does answer alot of questions, am not sure why the original code had the order by passed, it is something I will likely change. (Inherited this function).
• Erwin Brandstetter: @rdbmsNoob Passing (parts of) the ORDER BY clause is not a problem per se. But the way you do it is a problem. Consider the added notes above.

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

• rdbmsNoob: Thanks @lennart-slava-ukraini that indeed does make a difference in the way the original function was. A redesign is likely going to happen to make it more efficient.
• Lennart - Slava Ukraini: @erwins solution is way better than mine, but I'll leave this one open anyhow.