CODEWITHSUNDEEP
sqlpostgresql
Lorenzo Ang
Lorenzo Ang

Reputation: 1318

How to use LAG() within a user-defined function?

I'm trying to call Lag($1, 1) within a function to refer to an older value however I just get a null value instead. Is it invalid to call Lag() on $1 and, if so, is there an alternative?

Query if anyone wants to try:

CREATE OR REPLACE FUNCTION lag_test(decimal, rn decimal) 
RETURNS decimal LANGUAGE SQL AS $$

    SELECT CASE WHEN rn % 5 = 0 THEN LAG($1, 1) OVER ()
        ELSE $1 * 2
    END;
$$;

DO $$ BEGIN
    CREATE AGGREGATE lagt ( decimal ) (
      INITCOND = 1,
      SFUNC = lag_test,
      STYPE = decimal
    );
    EXCEPTION
        WHEN duplicate_function THEN NULL;
END $$;


WITH dat AS (
    SELECT row_number() OVER () as rn, num
    FROM generate_series(2, 20, 2) num
)
SELECT a.*, 
    lagt(a.rn) OVER (ORDER BY a.rn)
FROM dat a

+-----+-----+------+
| rn  | num | lagt |
+-----+-----+------+
|   1 |   2 |    2 |
|   2 |   4 |    4 |
|   3 |   6 |    8 |
|   4 |   8 |   16 |
|   5 |  10 |      |
| ... | ... |  ... |
+-----+-----+------+

I expected to get 8 however I got a null value instead

Upvotes: 1

Views: 283

Answers (1)

D-Shih
D-Shih

Reputation: 46239

PostgreSQL LAG() function provides access to a row that comes before the current row at a specified physical offset. In other words, from the current row, the LAG() function can access data of the previous row, or the row before the previous row, and so on.

I think you can't do that because of LAG function access data of the previous row but in function, lag_test didn't know what's the data of the previous row (by parameter)

So you will get NULL.

I think you this query will work for you.

WITH dat AS (
    SELECT row_number() OVER () as rn, num
    FROM generate_series(2, 20, 2) num
)
SELECT a.*, 
    CASE WHEN rn % 5 = 0 THEN LAG(num) OVER(ORDER BY a.rn)
        ELSE num * 2 END
FROM dat a

Upvotes: 2

Related Questions