CTE and function

[Gerhard Wiesinger <lists@xxxxxxxxxxxxx>]

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/



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general