CODEWITHSUNDEEP
sqlpostgresqlplpgsqlsql-function
Haris Muzaffar
Haris Muzaffar

Reputation: 443

How to save a result from postgres function into a variable?

I have a Postgres function which should return a column.

CREATE FUNCTION  fillvals_v1 (source_column numeric , order_by_col numeric) RETURNS numeric AS $$
    DECLARE
      fv_value numeric;
    BEGIN
        BEGIN
              select
                  first_value(src_col) over (partition by partition_col) as corrected_col
                      from (
                          select source_column as src_col, order_by_col,
                              sum(case when source_column is not null then 1 end) over (order by order_by_col) as partition_col
                                  from table_name
              ) t into fv_value;
              return cast(fv_value as numeric);
        END;
    END;
$$ LANGUAGE plpgsql STABLE;

The table, table_name is having values like this

seq | close
-------+-------
     1 |     3
     2 |     4
     3 |     5
     4 |
     5 |
     6 |     3
(6 rows)

So when i call this function like this

select fillvals_v1(close, seq) from table_name;

it gives me the below result

fillvals_v1
-------------
           3
           4
           5


           3
(6 rows)

which is not correct.

The actual result I want is

   seq | close
-------+-------
     1 |     3
     2 |     4
     3 |     5
     4 |     5
     5 |     5
     6 |     3
(6 rows)

That is, I want all the gaps filled with the latest values (ordered by seq) that is not NULL.

Can anyone tell me what is going wrong here? Maybe there is something that is missing in my function, or the way I save the function result into the variable might not be correct.

Upvotes: 1

Views: 1047

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

What you want is the IGNORE NULLS option on LAG(). Alas, Postgres does not (yet?) support that.

I would recommend:

select t.*, max(close) over (partition by grp)       
from (select t.*,
             count(close) over (order by seq) as grp
      from tablename t
     ) t;

You can also use a lateral join (or subquery):

select t.seq,
       coalesce(t.close, tprev.close) as close
from tablename t left join lateral
     (select tprev.*
      from t tprev
      where tprev.seq < t.seq and
            t.close is null and
            tprev.close is not null
      order by tprev.seq desc
     ) tprev;

The first method should be faster, though.

Upvotes: 1

Related Questions