CTE and function - Mailing list pgsql-general

Hello,

I'm trying to convert a select after a CTE into a function for generic
use. The CTE is normally a complex query but I want to capsulate then
the calculation of the Gini coefficient it into a function:
Based on:
http://www.heckler.com.br/blog/2010/06/15/gini-coeficient-having-fun-in-both-sql-and-python/
Details at: https://en.wikipedia.org/wiki/Gini_coefficient

================================================================================================================================================================
= OK

================================================================================================================================================================
WITH tab AS (
   SELECT unnest(ARRAY[1,2,3,4]) AS col
)
SELECT
    ((N+1.0)/(N-1.0)) - ((PiXi_sum/(N*(N-1)*u))*2.0) AS gini_coefficient
FROM
   ( SELECT
       SUM(PiXi) AS PiXi_sum,
       COUNT(*) AS N,
       (SELECT AVG(col) FROM tab) AS u
     FROM
       ( SELECT
           row_number() OVER() * col AS PiXi
         FROM
           (SELECT col FROM tab ORDER BY col DESC) t1
       ) t2
   ) t3
;


================================================================================================================================================================
= OK: Create function

================================================================================================================================================================
CREATE OR REPLACE FUNCTION gini_coefficient(IN table_name text, IN
column_name text, OUT gini_coefficient DOUBLE PRECISION) AS $$
BEGIN
EXECUTE format('
SELECT
    ((N+1.0)/(N-1.0)) - ((PiXi_sum/(N*(N-1)*u))*2.0) AS gini_coefficient
FROM
   ( SELECT
       SUM(PiXi) AS PiXi_sum,
       COUNT(*) AS N,
       (SELECT AVG(%s) FROM %s) AS u
     FROM
       ( SELECT
           row_number() OVER() * col AS PiXi
         FROM
           (SELECT %s FROM %s ORDER BY %s DESC) t1
       ) t2
   ) t3
;
', column_name, table_name, column_name, table_name, column_name)
INTO gini_coefficient;
END
$$ LANGUAGE plpgsql;


================================================================================================================================================================
= NOT OK:

================================================================================================================================================================
WITH tab AS (
   SELECT unnest(ARRAY[1,2,3,4]) AS col
)
SELECT gini_coefficient('tab', 'col');

ERROR:  relation "tab" does not exist
LINE 13:           (SELECT col FROM tab ORDER BY col DESC) t1


================================================================================================================================================================
= NOT OK:

================================================================================================================================================================
WITH tab AS (
   SELECT unnest(ARRAY[1,2,3,4]) AS col
)
SELECT * FROM gini_coefficient('tab', 'col');

ERROR:  relation "tab" does not exist
LINE 13:           (SELECT col FROM tab ORDER BY col DESC) t1

So it looks like the table tab from the CTE is not available in the
function.

Any ideas how to solve it and an explaination would be fine?

Thank you.

Ciao,
Gerhard

--
https://www.wiesinger.com/