Would like to build a materialised view in postgres but have only one issue.

I have a query which works if I specify a user directly. So this query would return me the results for user == 'reee'

   productAggregate.client AS client
            WHEN orders.order_type = 'Client Sell' 
            THEN orders.size_traded * '-1'::integer::numeric
            ELSE orders.size_traded
         END) AS outstandingAmount,
        WHEN count(DISTINCT orders.client_id) > 1 THEN 'Mixed'
        ELSE COALESCE(MAX(orders.client_id), '')
     END AS client,
     orders.product_id AS productId
 FROM  "my-product_orders" orders 
      orders.sales_credit_recipient_id in (SELECT uc."child_user_id" 
                                       FROM   "my-product_user_child" as uc 
                                       WHERE  uc."user_id" = 'reee') 
      OR orders.sales_credit_recipient_id = 'reee'
 GROUP BY orders.product_id
 ) productAggregate,
 "my-product_products" products
WHERE productAggregate.productId = products.id;

And I have another query which gives me a list of distinct userIds.

So I would like to execute the first query for each user (first query will return multiple rows so cannot use the double select. Need it all in one query for the view.

For normal join I would assume I need the userId column in first query but don't have it just because I would need to groupBy userId as well which is not what I want.


CREATE TABLE public."my-product_orders" (
    order_id character varying NOT NULL,
    product_id character varying NOT NULL,
    sales_credit_recipient_id character varying,
    order_type character varying NOT NULL,
    size_traded numeric(21,2) NOT NULL,
    client_id character varying,

CREATE TABLE public."my-product_products" (
    id character varying NOT NULL,
    isin character varying

CREATE TABLE public."my-product_user_child" (
    user_id character varying NOT NULL,
    child_user_id character varying NOT NULL

INSERT INTO public."my-product_products" VALUES ('PRODUCT1', 'ISIN1');
INSERT INTO public."my-product_products" VALUES ('PRODUCT2', 'ISIN2');

INSERT INTO public."my-product_orders" VALUES ('ORDER1', 'PRODUCT1', 'USER1', 'Client Sell', 100.00, '781');
INSERT INTO public."my-product_orders" VALUES ('ORDER2', 'PRODUCT1', 'USER2', 'Client Buy', 150.00, '386');
INSERT INTO public."my-product_orders" VALUES ('ORDER3', 'PRODUCT2', 'USER1', 'Client Buy', 50.00, '268');

INSERT INTO public."my-product_user_child" VALUES ('USER1', 'USER2');

Result of a view:

'ISIN1', 'USER1', '50.00', 'Mixed'
'ISIN1', 'USER2', '150.00', '386'
'ISIN2', 'USER1', '50.00', '286'
  • 2
    The same as on your previous post on Stack Overflow applies: You should illustrate your question by editing it and adding the relevant schema as CREATE TABLE statements, sample data as INSERT INTO statements and the desired result the sample data should give. Just in case, knowing the version of Postgres this is for, won't harm either. – sticky bit Jan 14 at 10:18
  • @stickybit added :) – Reeebuuk Jan 14 at 12:49
  • How is the query you posted related to the user "reee"? Do you mean the user "jaila"? – Lennart Jan 14 at 13:03
  • @Lennart fixed :) using reee now. – Reeebuuk Jan 14 at 13:15
  • 3
    Unrelated, but: you should really avoid names that require double-quotes to use them. Those will give you much more problems in the long run than they are worth it. – a_horse_with_no_name Jan 14 at 14:23

I'm not sure I understand what problem you are trying to solve, but I'll guess something like:

SELECT users.user_id
     , SUM(CASE WHEN orders.order_type = 'Client Sell' 
                THEN orders.size_traded*'-1'::integer::numeric
                ELSE orders.size_traded
           END) AS outstandingAmount
     , CASE WHEN count(DISTINCT orders.client_id) > 1 
            THEN 'Mixed'
            ELSE COALESCE(MAX(orders.client_id), '')
       END AS client
     , orders.product_id AS productId
FROM ( [your select of distinct users] ) as users (user_id)
    SELECT child_user_id 
    FROM my-product_user_child uc
    WHERE uc.user_id = users.user_id 
    SELECT users.user_id
) T (user_id)
JOIN my-product_orders AS orders
    ON orders.sales_credit_recipient_id = T.user_id
GROUP BY users.user_id, orders.product_id

will give you the outstanding amount per user in your select. I'm not sure what the purpose of the outer select is, so I'll leave that.

  • Excellent, seems to be working as expected. CROSS JOIN LATERAL, nice one :) – Reeebuuk Jan 15 at 9:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.