Question 113970 in PostgreSQL Question posted in
The official documentation can be found here.

Send timestamp array parameter to PostgreSQL function

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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);

    The explanation of Adrian Klaver fixed my function issue also:

    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 unnest(dates_array) as x;
        return max_date_time;
    END;
$$
;


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

  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search