Send timestamp array parameter to PostgreSQL function-CodePudding

i have developed a pgplsql function to receive array of dates the return the maximum date, but its not able to call the function:

select 1, arcfm.array_max_date('{2022-02-03, 2022-06-05}'::timestamp[]) as max_date_time;


CREATE OR REPLACE FUNCTION arcfm.array_max_date(in dates_array timestamp[])    
RETURNS timestamp    
LANGUAGE plpgsql
AS $$    
declare         
max_date_time timestamp;    
BEGIN        
SELECT max(x) into max_date_time FROM dates_array as x;        
return max_date_time;    
END;
$$
;

tried calls like: select 1, arcfm.array_max_date('{2022-02-03, 2022-06-05}'::timestamp[]) as max_date_time;

and expected the maximum date but it gives call error

SQL Error [42P01]: ERROR: relation "dates_array" does not exist Where: PL/pgSQL function array_max_date(timestamp without time zone[]) line 5 at SQL statement

CodePudding user response：

You can use unnest() to convert the array to a set of rows, and then use max() to get the maximum value:

select max(x)
from unnest('{2022-02-03, 2022-06-05}'::timestamp[]) as x;

CodePudding user response：

The better answer as suggested by Fastnlight is by using built-in function called "GREATEST"

SELECT GREATEST('2022-02-03'::timestamp, '2022-06-05'::timestamp);