Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

/*
Postgres: Create view with join depending on table of another join
Asked today
Modified today
Viewed 32 times
0

Lets say I have tables Article (ID, Name), Client (ID, Name), Invoice (ID, Date, Client) and InvoicePosition (ID, Invoice, Article, Quantity, Price). Each Invoice can only contain one InvoicePosition for each article. Now I would like to select an InvoicePosition with details of the previous InvoicePosition (same client, same article). Basically:

PreviousInvoicePosition.Article = InvoicePosition.Article
PreviousInvoicePosition.Invoice.Client = InvoicePosition.Invoice.Client
PreviousInvoicePosition.Invoice.Date < InvoicePosition.Invoice.Date
Select only the first PreviousInvoicePosition, ordered by Date descending.
Ideally this should be created as a view so I can then select all InvoicePositions (including the PreviousInvoicePosition) for one invoice, using a WHERE clause.

If it matters: This tables and the view are part of a supabase project and are then queried using PostgREST.

Is this possible?
*/
create table Article (
  ID int generated by default as identity primary key, 
  Name text);
create table Client (
  ID int generated by default as identity primary key, 
  Name text);
create table Invoice (
  ID int generated by default as identity primary key, 
  Date date, 
  Client int references client(id));
create table InvoicePosition (
  ID int generated by default as identity primary key, 
  Invoice int references invoice(id), 
  Article int references article(id), 
  Quantity numeric, 
  Price numeric,
  constraint each_invoice_can_only_contain_one_invoiceposition_for_each_article
    unique (Invoice,Article));

insert into article(id,name) values
  (1,'article1'),
  (2,'article2'),
  (3,'article3');
insert into client(id,name) values
  (1,'client1'),
  (2,'client2'),
  (3,'client3');
insert into invoice(id,date,client) values
  (1,'yesterday',1),    -- Invoice by Client 1
  (2,'today',1),        -- Invoice by Client 1
  (3,'tomorrow',1),     -- Invoice by Client 1
  (4,'yesterday', 2);   -- Invoice by Client 2
insert into InvoicePosition values
  (1,1,1,11.0,77.0),    -- Invoice from yesterday by client 1 with article 1
  (2,1,2,22.0,88.0),    -- Invoice from yesterday by client 1 with article 2
  (3,1,3,33.0,99.0),    -- Invoice from yesterday by client 1 with article 3
  (4,2,1,12.0,78.0),    -- Invoice from today by client 1 with article 1
  (5,2,2,23.0,89.0),    -- Invoice from today by client 1 with article 2
  (6,4,1,34.0,100.0);   -- Invoice from yesterday by client 2 with article 1
/*  
Now I would like to select an InvoicePosition with details of the previous 
InvoicePosition (same client, same article). Basically:

PreviousInvoicePosition.Article = InvoicePosition.Article
PreviousInvoicePosition.Invoice.Client = InvoicePosition.Invoice.Client
PreviousInvoicePosition.Invoice.Date < InvoicePosition.Invoice.Date
Select only the first PreviousInvoicePosition, ordered by Date descending.

InvoicePosId = 4
InvoiceId = 2
ArticleID = 1
Quantity = 12.0
Price = 78.0
LastInvoicePosId = 1    // Invoice from yesterday from client 1 with article 1
*/

CREATE TABLE



CREATE TABLE



CREATE TABLE



CREATE TABLE



INSERT 0 3



INSERT 0 3



INSERT 0 4



INSERT 0 6


create view invoices_with_previous as 
select ip.id       as "InvoicePosId",
       i.id        as "InvoiceId",
       ip.article  as "ArticleID",
       ip.quantity as "Quantity",
       ip.price    as "Price",
       lag(ip.id)over w1 as "LastInvoicePosId"
from InvoicePosition  as  ip 
  join invoice i on i.id =ip.invoice
window w1 as (partition by i.client, ip.article
              order by i.date)
order by ip.id;

select * from invoices_with_previous;

CREATE VIEW




InvoicePosId
InvoiceId
ArticleID
Quantity
Price
LastInvoicePosId


1
1
1
11.0
77.0
null

2
1
2
22.0
88.0
null

3
1
3
33.0
99.0
null

4
2
1
12.0
78.0
1

5
2
2
23.0
89.0
2

6
4
1
34.0
100.0
null



SELECT 6


create or replace view invoices_with_previous as 
select ip.id       as "InvoicePosId",
       i.id        as "InvoiceId",
       a.id        as "ArticleID",
       ip.quantity as "Quantity",
       ip.price    as "Price",
       lag(ip.id)over w1 as "LastInvoicePosId"
from InvoicePosition  as  ip 
  join invoice i on i.id =ip.invoice
  join client  c on c.id = i.client
  join article a on a.id =ip.article
window w1 as (partition by c.id, a.id 
              order by i.date)
order by ip.id;

select * from invoices_with_previous;

CREATE VIEW




InvoicePosId
InvoiceId
ArticleID
Quantity
Price
LastInvoicePosId


1
1
1
11.0
77.0
null

2
1
2
22.0
88.0
null

3
1
3
33.0
99.0
null

4
2
1
12.0
78.0
1

5
2
2
23.0
89.0
2

6
4
1
34.0
100.0
null



SELECT 6


select jsonb_pretty(jsonb_build_object('InvoicePosition',ip,'Invoice',i,
                                       'Client',c,'Article',a)) this,
       jsonb_pretty(lag(jsonb_build_object('InvoicePosition',ip,'Invoice',i,
                                           'Client',c,'Article',a))over w1) prev
from InvoicePosition  as  ip 
  join invoice i on i.id =ip.invoice
  join client  c on c.id = i.client
  join article a on a.id =ip.article
window w1 as (partition by c.id, a.id 
              order by date)
order by ip.id;


this
prev


{
    "Client": {
        "id": 1,
        "name": "client1"
    },
    "Article": {
        "id": 1,
        "name": "article1"
    },
    "Invoice": {
        "id": 1,
        "date": "2024-03-06",
        "client": 1
    },
    "InvoicePosition": {
        "id": 1,
        "price": 77.0,
        "article": 1,
        "invoice": 1,
        "quantity": 11.0
    }
}
null

{
    "Client": {
        "id": 1,
        "name": "client1"
    },
    "Article": {
        "id": 2,
        "name": "article2"
    },
    "Invoice": {
        "id": 1,
        "date": "2024-03-06",
        "client": 1
    },
    "InvoicePosition": {
        "id": 2,
        "price": 88.0,
        "article": 2,
        "invoice": 1,
        "quantity": 22.0
    }
}
null

{
    "Client": {
        "id": 1,
        "name": "client1"
    },
    "Article": {
        "id": 3,
        "name": "article3"
    },
    "Invoice": {
        "id": 1,
        "date": "2024-03-06",
        "client": 1
    },
    "InvoicePosition": {
        "id": 3,
        "price": 99.0,
        "article": 3,
        "invoice": 1,
        "quantity": 33.0
    }
}
null

{
    "Client": {
        "id": 1,
        "name": "client1"
    },
    "Article": {
        "id": 1,
        "name": "article1"
    },
    "Invoice": {
        "id": 2,
        "date": "2024-03-07",
        "client": 1
    },
    "InvoicePosition": {
        "id": 4,
        "price": 78.0,
        "article": 1,
        "invoice": 2,
        "quantity": 12.0
    }
}
{
    "Client": {
        "id": 1,
        "name": "client1"
    },
    "Article": {
        "id": 1,
        "name": "article1"
    },
    "Invoice": {
        "id": 1,
        "date": "2024-03-06",
        "client": 1
    },
    "InvoicePosition": {
        "id": 1,
        "price": 77.0,
        "article": 1,
        "invoice": 1,
        "quantity": 11.0
    }
}

{
    "Client": {
        "id": 1,
        "name": "client1"
    },
    "Article": {
        "id": 2,
        "name": "article2"
    },
    "Invoice": {
        "id": 2,
        "date": "2024-03-07",
        "client": 1
    },
    "InvoicePosition": {
        "id": 5,
        "price": 89.0,
        "article": 2,
        "invoice": 2,
        "quantity": 23.0
    }
}
{
    "Client": {
        "id": 1,
        "name": "client1"
    },
    "Article": {
        "id": 2,
        "name": "article2"
    },
    "Invoice": {
        "id": 1,
        "date": "2024-03-06",
        "client": 1
    },
    "InvoicePosition": {
        "id": 2,
        "price": 88.0,
        "article": 2,
        "invoice": 1,
        "quantity": 22.0
    }
}

{
    "Client": {
        "id": 2,
        "name": "client2"
    },
    "Article": {
        "id": 1,
        "name": "article1"
    },
    "Invoice": {
        "id": 4,
        "date": "2024-03-06",
        "client": 2
    },
    "InvoicePosition": {
        "id": 6,
        "price": 100.0,
        "article": 1,
        "invoice": 4,
        "quantity": 34.0
    }
}
null



SELECT 6

InvoicePosId	InvoiceId	ArticleID	Quantity	Price	LastInvoicePosId
1	1	1	11.0	77.0	null
2	1	2	22.0	88.0	null
3	1	3	33.0	99.0	null
4	2	1	12.0	78.0	1
5	2	2	23.0	89.0	2
6	4	1	34.0	100.0	null

this	prev
{ "Client": { "id": 1, "name": "client1" }, "Article": { "id": 1, "name": "article1" }, "Invoice": { "id": 1, "date": "2024-03-06", "client": 1 }, "InvoicePosition": { "id": 1, "price": 77.0, "article": 1, "invoice": 1, "quantity": 11.0 } }	null
{ "Client": { "id": 1, "name": "client1" }, "Article": { "id": 2, "name": "article2" }, "Invoice": { "id": 1, "date": "2024-03-06", "client": 1 }, "InvoicePosition": { "id": 2, "price": 88.0, "article": 2, "invoice": 1, "quantity": 22.0 } }	null
{ "Client": { "id": 1, "name": "client1" }, "Article": { "id": 3, "name": "article3" }, "Invoice": { "id": 1, "date": "2024-03-06", "client": 1 }, "InvoicePosition": { "id": 3, "price": 99.0, "article": 3, "invoice": 1, "quantity": 33.0 } }	null
{ "Client": { "id": 1, "name": "client1" }, "Article": { "id": 1, "name": "article1" }, "Invoice": { "id": 2, "date": "2024-03-07", "client": 1 }, "InvoicePosition": { "id": 4, "price": 78.0, "article": 1, "invoice": 2, "quantity": 12.0 } }	{ "Client": { "id": 1, "name": "client1" }, "Article": { "id": 1, "name": "article1" }, "Invoice": { "id": 1, "date": "2024-03-06", "client": 1 }, "InvoicePosition": { "id": 1, "price": 77.0, "article": 1, "invoice": 1, "quantity": 11.0 } }
{ "Client": { "id": 1, "name": "client1" }, "Article": { "id": 2, "name": "article2" }, "Invoice": { "id": 2, "date": "2024-03-07", "client": 1 }, "InvoicePosition": { "id": 5, "price": 89.0, "article": 2, "invoice": 2, "quantity": 23.0 } }	{ "Client": { "id": 1, "name": "client1" }, "Article": { "id": 2, "name": "article2" }, "Invoice": { "id": 1, "date": "2024-03-06", "client": 1 }, "InvoicePosition": { "id": 2, "price": 88.0, "article": 2, "invoice": 1, "quantity": 22.0 } }
{ "Client": { "id": 2, "name": "client2" }, "Article": { "id": 1, "name": "article1" }, "Invoice": { "id": 4, "date": "2024-03-06", "client": 2 }, "InvoicePosition": { "id": 6, "price": 100.0, "article": 1, "invoice": 4, "quantity": 34.0 } }	null