Re: CTE and function

On Thu, Feb 25, 2016 at 3:31 AM, Gerhard Wiesinger <lists(at)wiesinger(dot)com>
wrote:

>
> ================================================================================================================================================================
> = 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.