CTE and function
От | Gerhard Wiesinger |
---|---|
Тема | CTE and function |
Дата | |
Msg-id | 56CED803.5070505@wiesinger.com обсуждение исходный текст |
Ответы |
Re: CTE and function
(Ben Primrose <bprimrose@tracelink.com>)
Re: CTE and function ("David G. Johnston" <david.g.johnston@gmail.com>) |
Список | 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/
В списке pgsql-general по дате отправления:
Предыдущее
От: Karsten HilbertДата:
Сообщение: Re: check constraint problem during COPY while pg_upgrade-ing