Reference a view inside of the view in postgresql

Question

I would like to do something like the following:

CREATE VIEW foo_view AS
    SELECT
            foo.id,
            foo.carryover_id,
            foo.amount,
            (SELECT SUM(foo_view.total)
                FROM foo_view
                WHERE foo_view.carryover_id = foo.id
            ) AS carryover_amount,
            (amount + carryover_amount) AS total;

However, this raises an error relation "foo_view" does not exist. I would think that RECURSIVE would come in handy here, but the documentation is more reference than tutorial and there don't seem to be any resources out there to help me. Thoughts?

EDIT

Here's a sqlfiddle with a relevant schema http://sqlfiddle.com/#!15/6834d/1

So how can you select something from a view that does not exist yet? — zerkms, May 02 '15 at 00:21
Can you provide an example table with what you expect the results to be. May not even need recursive. — Rogala, May 02 '15 at 00:22
@zerkms - you can't. You can create the view, then alter the view to reference itself, but when you select from it, it will blow up, because views cannot reference themselves. This would cause an infinite loop because there is no limit set. — Rogala, May 02 '15 at 00:23
@zerkms, @Rogala: `carryover_id` will never point to the referencing foo_view, so if it evaluated the views recursively, there would be no problem. — astex, May 02 '15 at 00:26
@Rogala: I want to create a view that grabs carried over totals from other rows in the view and adds them together to make the total in the current row in the view. — astex, May 02 '15 at 00:28
Does this help: http://stackoverflow.com/questions/663154/copy-data-from-one-existing-row-to-another-existing-row-in-sql — Rogala, May 02 '15 at 00:35
@astex When you say you want to carry over totals, are you looking for a running totals scenario? — Rogala, May 02 '15 at 00:37
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/76742/discussion-between-astex-and-rogala). — astex, May 02 '15 at 00:37

score 2 · Answer 1 · edited Sep 17 '21 at 06:54

You are right, your article is what you needed.

I formatted the data first into a fooBar temp table:

 SELECT b.id
      , foo_id
      , ISNULL(f.carryover_id, foo_id) as carryover_id
      , b.amount
      , ROW_NUMBER() OVER(
           PARTITION BY ISNULL(f.carryover_id, foo_id) 
           ORDER BY b.id asc) as rowNmbr
 INTO #fooBar
 FROM #bar as b
      INNER JOIN #foo as f
      ON b.foo_id = f.id;

The first row in the WITH is the top level. Then it recursively goes through each level. The rowNmbr is the level.

 WITH summarizedFoo(rowNmbr, id, foo_id, carryover_id, amount, totalAmount) 
 AS 
 (
     SELECT f.rowNmbr
           , f.id
           , f.foo_id
           , f.carryover_id
           , ISNULL(f.amount,0) as amount
           , ISNULL(f.amount,0) as totalAmount
      FROM #fooBar as f 
      WHERE f.rowNmbr = 1
 UNION ALL    
      SELECT f.rowNmbr
           , f.id
           , f.foo_id
           , f.carryover_id
           , ISNULL(f.amount,0) as amount
           , CAST((ISNULL(f.amount,0) 
                +  ISNULL(s.totalAmount,0)) as decimal) as totalAmount
      FROM #fooBar as f
           INNER JOIN summarizedFoo as s
           ON f.carryover_id = s.carryover_id
               AND f.rowNmbr = s.rowNmbr +1
 )
 SELECT rowNmbr, id, foo_id, carryover_id, amount, totalAmount
 FROM summarizedFoo

I did this in T-SQL, but to make this work in your fiddler

Here is the PostgreSQL version: Create a fooBar table (could be a view):

INSERT INTO fooBar
SELECT CAST(b.id as INTEGER)
, CAST(foo_id as INTEGER)
, CAST(CASE WHEN f.carryover_id is NULL THEN foo_id ELSE f.carryover_id END as INTEGER) as carryover_id
, CAST(b.amount as DECIMAL)
, CAST(ROW_NUMBER() OVER(PARTITION BY CASE WHEN f.carryover_id is NULL THEN foo_id ELSE f.carryover_id END ORDER BY b.id asc) as INTEGER) as rowNmbr
FROM bar as b
INNER JOIN foo as f
ON b.foo_id = f.id;

Here is the WITH RECURSIVE:

WITH RECURSIVE summarizedFoo(rowNmbr, id, foo_id, carryover_id, amount, totalAmount) 
AS 
(
    SELECT f.rowNmbr, f.id, f.foo_id, f.carryover_id
      , f.amount
      , f.amount
    FROM fooBar as f    
    WHERE f.rowNmbr = 1
UNION ALL
    SELECT f.rowNmbr, f.id, f.foo_id, f.carryover_id
       , f.amount
       , f.amount +  s.totalAmount
    FROM fooBar as f
    INNER JOIN summarizedFoo as s
    ON f.carryover_id = s.carryover_id
        AND f.rowNmbr = s.rowNmbr +1
)
SELECT rowNmbr, id, foo_id, carryover_id, amount, totalAmount
FROM summarizedFoo

I tested in SSMS(T-SQL version) and Fiddler(PostgreSQL), and verified the results in Excel.

Here is Fiddler of the solution: http://sqlfiddle.com/#!15/a7822/3

astex · Accepted Answer · 2015-05-02T03:11:06.360

1

The trick is to populate a table with source > target pairs, then to use that table to calculate the total amount:

CREATE RECURSIVE VIEW leaf_view(target_id, source_id) AS (
    SELECT id, id FROM foo
  UNION ALL
    SELECT leaf_view.target_id, foo.id
    FROM leaf_view
    JOIN foo ON foo.carryover_id = leaf_view.source_id
);

CREATE VIEW foo_view(id, total) AS
    SELECT leaf_view.target_id, SUM(bar.amount)
        FROM leaf_view
        LEFT JOIN bar ON leaf_view.source_id = bar.foo_id
        GROUP BY leaf_view.target_id;

http://sqlfiddle.com/#!15/6834d/62

I owe this answer to RhodiumToad in #postgresql on freenode. Note that this will have problems for large foo since the optimizer generally can't work on recursive queries.

edited May 02 '15 at 03:11

answered May 02 '15 at 03:06

astex

1,045
10
28

I like your answer a lot better! Nice! – Rogala May 02 '15 at 03:11
Yeah yours was a little long. It remains to be seen if this will be performant enough to see production. =\ – astex May 02 '15 at 03:13
Hey, if your's doesn't work, you at least have something to fall back on. :) Length of code isn't always everything. It all comes down to speed. I love to hide long scripts in these things called view, stored procedures, and function... :) Have a good night. – Rogala May 02 '15 at 03:17
On another note, you fiddler does' give the results you requested at the top. Not sure if that matters, since you have it doing what you want it to do. – Rogala May 02 '15 at 03:19

Raman · Answer 3 · 2015-05-02T01:22:05.703

-1

Editing my answer as below.. I realized you are trying to do something recursive, does this help?? It'd really help if you gave a sample input and the output that you are expecting..

WITH RECURSIVE foo_view(id, total) AS (
    SELECT id, amount from foo;
  UNION ALL
    SELECT foo_view.id, sum(foo_view.total+foo.amount)
    FROM foo 
    JOIN
        foo_view
    ON
        foo_view.id = foo.carryover_id
  )
SELECT * from foo_view

edited May 02 '15 at 01:22

answered May 02 '15 at 00:39

Raman

19
3

The total column is not defined on the view, so I don't see how you could select it. – astex May 02 '15 at 00:41
Can you explain your question with an example? – Raman May 02 '15 at 01:11
Even modified to remove syntax errors, this doesn't work. See http://sqlfiddle.com/#!15/6834d/7. – astex May 02 '15 at 01:29
I think [this](http://sqlfiddle.com/#!15/6834d/40) is what you were going for, but it still doesn't quite get the right results. – astex May 02 '15 at 01:49

score -1 · Answer 4 · answered May 02 '15 at 00:53

-1

It looks like you want to have a cumulative running total amount. You could get this with a self-join, or a sub query, e.g:

select
  id,
  amount,
  (select sum amount from foo b where b.id <= a.id ) as cumulative
from foo a

or

select
  a.id,
  a.amount,
  sum(b.amount) as cumulative
from foo a
join foo b on b.id <= a.id
group by a.id, a.amount

answered May 02 '15 at 00:53

Kris Winquist

1

No, the carryover_id is arbitrary and manual as stated, not linear an automated as you've inferred. – astex May 02 '15 at 00:55

Reference a view inside of the view in postgresql

4 Answers4