How can I parse query result to string in postgres?

Question

I prepared sql fiddle: http://sqlfiddle.com/#!15/62e65/2

And schema is here:

CREATE TABLE products
    ("id" int, "name" varchar(5))
;

INSERT INTO products
    ("id", "name")
VALUES
    (1, 'Car'),
    (2, 'Phone')
;

CREATE TABLE operations
    ("id" int, "product_id" int, "status" varchar(7), "type" varchar(8))
;

INSERT INTO operations
    ("id", "product_id", "status", "type")
VALUES
    (1, 1, 'pending', 'invoice'),
    (2, 1, 'done', 'delivery'),
    (3, 2, 'done', 'delivery'),
    (3, 2, 'done', 'invoice')
;

I know that the data schema could be better, but I don't have possibility to refactor it now - I am just adding new view. Note about schema: product has always 2 operations: invoicing and delivery. What I want to achieve is to get such result:

name status
car pending
phone done

Where product status is a string returned after checking both product operations.
Rule is that product status is done only when both operations are done, otherwise its pending.
How to write such query in postgres?

Can you please add the SQL statements from the SQLFiddle to your question. SQLFiddle currently doesn't work (for me) — user330315
– user330315, Commented Oct 30, 2015 at 9:43

Gabriel's Messanger · Accepted Answer · 2015-10-30 11:20:29Z

1

SELECT p.name, CASE WHEN status='done' THEN 'done' else 'pending' END
FROM (
    SELECT p.id, p.name, string_agg(distinct o.status, '') as status
    FROM products p JOIN operations o ON o.product_id = p.id
    GROUP BY p.id, p.name
) sub

Fisrtly we concatenate distinct values in one string (string_agg) and then in master query we check: if ne string is single 'done' that meand that all operations were 'done'. Otherwise one or both operation are 'pending'.

Since your foreign key is products.id <--> operations.product_id we must GROUP BY products.id.

edited Oct 30, 2015 at 11:20

answered Oct 30, 2015 at 10:05

Gabriel's Messanger

3,36822 silver badges32 bronze badges

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

Comments

Mureinik · Accepted Answer · 2015-10-30 09:56:53Z

You could create two subqueries, one for orders and one for deliveries, join them, and then use a case expression to check the status on both:

SELECT invoice.name, 
       CASE WHEN invoice.status = 'done' and delivery.status = 'done' THEN 'done' 
            ELSE 'pending' 
       END AS status
FROM   (SELECT p.name, p.id, o.status
        FROM   products p
        JOIN   operations o ON o.type = 'invoice' AND 
                               o.product_id = p.id) invoice
JOIN   (SELECT p.name, p.id, o.status
        FROM   products p
        JOIN   operations o ON o.type = 'delivery' AND
                               o.product_id = p.id) delivery
       ON invoice.id = delivery.id

stas.yaranov · Accepted Answer · 2015-10-30 11:13:16Z

0

select
    p.name,
    case
        when count(case when status <> 'done' then 1 else null end) = 0 then 'done'
        else 'pending'
    end status
  from products p
  inner join operations o on p.id = o.product_id
  group by p.name, p.id;

edited Oct 30, 2015 at 11:13

answered Oct 30, 2015 at 9:56

stas.yaranov

1,78711 silver badges17 bronze badges

2 Comments

Gabriel's Messanger Over a year ago

this grouping is wrong if product names aren't unique. Query should be more generic since we know nothing about name uniqueness,

stas.yaranov Over a year ago

Yes, I'm agree. Fixed.

Collectives™ on Stack Overflow

How can I parse query result to string in postgres?

3 Answers 3

Comments

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Related