querying over multiple data from function in PostgreSQL

Question

I have a function which gives stock levels:

select * from stocklevel(ID)

This function gives stock leverl per ID. For example:

select * from stocklevel(23)

Result is:

   ID  datelevel  stocklevel
   23   01.01.17   15
   23   02.01.17   18
   23   05.01.17   20

This is the stock level for ID=23.

Not all dates appear, it depends on many things.

I want to use this function to get the stock levels for all parts from my system

basically something like this:

select *,(select stocklevel from stocklevel(parts.partid) where datelevel = ??? )
from parts

OR:

  select *
  from parts
  left join ( select stocklevel from stocklevel(parts.partid) where datelevel = ??? ) using (ID)

Now, the issue is with the WHERE condition I want to specific a specific date like '04.01.17' but if the date does not exist i want it to take the date before that so:

for the ID=23 and date '04.01.17' it should show 18,'02.01.17' .

for the ID=23 and date '15.01.13' it should show nothing.

for the ID=23 and date '05.01.17' it should show 20,'05.01.17' .

How can I do that?

Gordon Linoff · Accepted Answer · 2017-04-02 12:12:26Z

1

First, I would use a lateral join:

select *
from parts p left join lateral
     stocklevel(p.partid);

Then the issue is that you want the most recent level before or on a given date. You can do this using distinct on:

select distinct on (p.id) . . .
from parts p left join lateral
     stocklevel(p.partid)
where datelevel <= ?
order by p.id, datelevel desc;

Note: This will not return parts that have no dates before the given date. You might want:

where datelevel is null or datelevel <= ?

edited Apr 2, 2017 at 12:12

answered Apr 2, 2017 at 12:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

4 Comments

avi Over a year ago

I'm not sure I understand the note... datelevel is never null. If date was skipped it won't have a record.

Gordon Linoff Over a year ago

@avi . . . I mean, if your date is earlier than any stock level for that part, then the part will not be returned. The NULL comes from the outer join.

avi Over a year ago

don't I need Using or On in the join?

Gordon Linoff Over a year ago

@avi . . . Not with a lateral join (a good place to start is with the documentation: postgresql.org/docs/current/static/…).

Luuk · Accepted Answer · 2017-04-02 12:09:37Z

0

select *
  from parts
  left join ( select stocklevel 
      from stocklevel(parts.partid) 
      where datelevel = (select max(datelevel) 
            from stocklevel(parts.partid) sl2 
            where sl2.datelevel <= '04.01.17') using(ID) ) using (ID)

untested...

answered Apr 2, 2017 at 12:09

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

Collectives™ on Stack Overflow

querying over multiple data from function in PostgreSQL

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related