================================================================================================================================================================
= 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?
Not tested but:
CREATE TEMP TABLE tab AS SELECT ... AS col;
SELECT * FROM gini_ciefficient('tab','col');
A function is able to access (session) global objects and whatever data is passed in to it via is parameters.
I don't know if there is any fundamental reason the contents of a CTE cannot be seen by a function executing in the same context but that is not how it works today.
So turn the CTE into its own standalone TABLE and you should be able to then refer to it by name in subsequent queries. It works for actual queries and so functions should be no different.
David J.
