Finding the sum of a column that contains a window function
<p>I have this select query:  </p>

<pre><code>SELECT 
    total,   
    COALESCE(total - Lag(total)OVER(ORDER BY total), 0) AS dif_total
FROM   ( select count(*) as total
    FROM
         tbl_person
    left join
         tbl_census
    on
          tbl_census.person_id = tbl_person.person_id
    group by extract(year from tbl_census.date)
    ) abc
</code></pre>

<p>Is there a way I could find the sum of the column dif_total?<br>
I can't use the Sum() because it contains a window function.  </p>

<p>I tried saving the column to an array because I figure maybe I could call the function and convert the array to a column then use Sum().<br>
But I messed it up.  </p>

<p>Here is my query for the function.  </p>

<pre><code>CREATE OR REPLACE function growth() Returns int[] as $$
declare total2 integer[];  
BEGIN  
      SELECT
           total,
           COALESCE(total - Lag(total)OVER(ORDER BY total), 0) into total2
      FROM
           ( select count(*) as total
            from
                 tbl_person
            group by extract(year from bdate)
            ) abc ;
RETURN total2;
END; $$ LANGUAGE plpgsql;
</code></pre>

<p>The function query runs successfully and does not show any warning or error but I think I was doing it wrong because when I try to SELECT it it will say </p>

<blockquote>
  <p>Array value must start with "{" or dimension information</p>
</blockquote>

<p>I'm very new in using stored function in postgre.<br>
What changes should I do to my function to work?
Or what are the other ways for me to sum the column dif_total above?</p>