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

Question

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.

This approach is very inefficient, since the function and the complicated query in it will be run for every result row. You had better write that in a single query. If you describe the desired result, we can help with that. — Laurenz Albe
– Laurenz Albe, Commented Jun 13, 2019 at 6:36

Gordon Linoff · Accepted Answer · 2019-06-13 12:07:17Z

1

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.

edited Jun 13, 2019 at 12:07

answered Jun 13, 2019 at 12:01

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related