PostgreSQL IGNORE NULLS in window functions

Question

On the left panel data without IGNORE NULLS.
On the right panel data with IGNORE NULLS.

So I need to get right variant in PostgreSQL

Need to emulate Oracle IGNORE NULLS in window functions (LEAD and LAG) in PostgreSQL.

SELECT empno,
   ename,
   orig_salary,
   LAG(orig_salary, 1, 0) IGNORE NULLS OVER (ORDER BY orig_salary) AS sal_prev
FROM   tbl_lead;

If there are NULL, it should return the latest not null value.

I've tried it via PostgreSQL user defined aggregate functions, but it's rather hard to understand methodology of it https://www.postgresql.org/docs/9.6/static/sql-createaggregate.html

The solution can't be realized via WITH clause or sub-query since it it used in complex query.

Answer 1

I have updated @klin 's answer. Below functions allows to pass anyelement, has offset and default parameters.

LAG ( expression [, offset [, default] ] )

create or replace function swf_lag_trans(anyarray, anyelement, integer, 
anyelement)
returns anyarray language plpgsql as $$
begin
if $1 is null then
    $1:= array_fill($4, array[$3+1]);
end if;
if $1[$3+1] is not null then 
for i in 1..$3 loop
        $1[i]:= $1[i+1];
        i := i+1;
    end loop;
    $1[$3+1]:= $2;
end if;
return $1;
end $$;
create or replace function swf_lag_final(anyarray)
returns anyelement language sql as $$
select $1[1];
$$;
create aggregate swf_lag(anyelement, integer, anyelement) (
sfunc = swf_lag_trans,
stype = anyarray,
finalfunc = swf_lag_final
);

And usage:

with my_table(name, salary) as (
values
    ('A', 100),
    ('B', 200),
    ('C', 300),
    ('D', null),
    ('E', null),
    ('F', null)
)

select 
    name, salary, 
    lag(salary, 2, 123) over (order by salary) prev_salary,
    swf_lag(salary, 2, 123)  over (order by salary) my_prev_salary
from my_table;

It works for me. Please, correct, if required.