0

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.

8
  • "... 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. Nov 11, 2022 at 17:28
  • @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.
    – rdbmsNoob
    Nov 17, 2022 at 11:18
  • Is this what you are trying to do ... order_placed_on >= (select current_date - 30) ... ? Nov 17, 2022 at 11:59
  • Your current code does something like ...order_placed_on >= 'select current_date - 30'... Nov 17, 2022 at 12:05
  • @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?
    – rdbmsNoob
    Nov 17, 2022 at 14:00

2 Answers 2

2

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:

(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:

2
  • 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).
    – rdbmsNoob
    Nov 18, 2022 at 12:21
  • @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. Nov 18, 2022 at 15:53
1

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

2
  • 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.
    – rdbmsNoob
    Nov 18, 2022 at 12:22
  • @erwins solution is way better than mine, but I'll leave this one open anyhow. Nov 18, 2022 at 12:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.