Measure the time it takes to execute a PostgreSQL query

Coming from an MSSQL background myself and now more often working in Postgres I feel your pain =)

The "trouble" with Postgres is that it supports only 'basic' SQL commands (SELECT, INSERT, UPDATE, CREATE, ALTER, etc...) but the moment you want to add logic (IF THEN, WHILE, variables, etc.) you need to switch to pl/pgsql which you can only use inside functions (AFAIK). From a TSQL POV there are quite some limitations and in fact, some things suddenly don't work anymore (or need to be done differently.. e.g. SELECT * INTO TEMPORARY TABLE tempTable FROM someTable will not work but CREATE TABLE tempTable AS SELECT * FROM someTable will)

Something I learned the hard way too is that CURRENT_TIMESTAMP (or Now()) will return the same value within a transaction. And since everything inside a function runs inside a transaction this means you have to use clock_timstamp()

Anyway, to answer your question, I think this should get you going:

CREATE OR REPLACE FUNCTION fn_test ( nbrOfIterations int)
RETURNS TABLE (iterations int, totalTime interval, secondsPerIteration int)
AS $$
DECLARE 

    i         int;
    startTime TIMESTAMP;
    endTime   TIMESTAMP;
    dummy     text;

BEGIN

    i := 1;
    startTime := clock_timestamp();

    WHILE ( i <=  nbrOfIterations) LOOP
    
        -- your query here
        -- (note: make sure to not return anything or you'll get an error)

        -- example:
        SELECT pg_sleep INTO dummy FROM pg_sleep(1);

        i := i + 1;

    END LOOP;

    endTime := clock_timestamp();

    iterations := nbrOfIterations;
    totalTime := (endTime - startTime);
    secondsPerIteration := (EXTRACT(EPOCH FROM endTime) - EXTRACT(EPOCH FROM startTime)) / iterations;


    RETURN NEXT;

END;
$$ language plpgsql;


SELECT * FROM fn_test(5);

While the accepted answer is correct, this tweaking of it worked better for me. Again, I want to emphasize this extra answer below is based on the above answer, and it would not be possible without it. It just works better in my own situation to use the tweak I made below.

The answer below is indeed almost entirely based on the accepted answer. However, I changed how the return is used and also seconds to milliseconds:

----------------------------------------------------------------------------------------------------
-- fn__myFunction_Q.sql
----------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------
-- DROP FUNCTION mySchema.fn__myFunction
--------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mySchema.fn__myFunction ( nbrOfIterations int)
RETURNS TABLE (iterations int, totalTime interval, millisecondsPerIteration int) -- interval --  
AS $$
declare

i         int;
startTime TIMESTAMP;
endTime   TIMESTAMP;
-- dummy     text;
iterations int;
millisecondsPerIteration int;
totalTime interval;

BEGIN

    i := 1;
    startTime := clock_timestamp();

    WHILE ( i <=  nbrOfIterations) LOOP
     PERFORM /* Put your query here, replacing SELECT with PERFORM */
--------------------------------------------------------------------------------------------
--SELECT 
    -- YOUR QUERY HERE 
    -- ...
--------------------------------------------------------------------------------------------
        i := i + 1; -- very important to increment loop counter, else one gets an infinite loop!!!

    END LOOP;

    endTime := clock_timestamp();

    iterations := nbrOfIterations;
    totalTime := (endTime - startTime);
    millisecondsPerIteration := 1000 * (EXTRACT(EPOCH FROM endTime) - EXTRACT(EPOCH FROM startTime)) / iterations;
    RETURN QUERY  select iterations, totalTime, millisecondsPerIteration; 
    -- RETURNS TABLE (iterations int, totalTime interval, secondsPerIteration int) -- interval --  

    -- RETURN NEXT;

END;
$$ language plpgsql;

--------------------------------------------------------------------------------------------

To call this function, just use:

SELECT * from mySchema.fn__myFunction(1000) as ourTableResult;