I'd like to get an array containing distinct values (always integers) form a column in a table that is provided as a parameter. So I created this function:
CREATE OR REPLACE FUNCTION get_distinct_values(table_name text, param_name text) RETURNS integer[] AS $BODY$ DECLARE _values integer[]; rec record; BEGIN
DROP TABLE IF EXISTS z; CREATE TEMP TABLE z(val integer);
FOR rec IN EXECUTE 'SELECT DISTINCT(' || param_name || ') AS z_val FROM ' || table_name || ';' LOOP IF rec IS NOT NULL THEN RAISE NOTICE 'rec=% ',rec; INSERT INTO z(val) VALUES(CAST(rec.z_val AS integer)); -- same result without the casting.. END IF; END LOOP; _values := ARRAY(SELECT val FROM z);
RETURN _values;
END $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; ALTER FUNCTION get_distinct_values(text, text) OWNER TO "admin";
Because non of these approaches works: i) EXECUTE 'ARRAY(SELECT... i) ARRAY(EXECUTE 'SELECT... i) EXECUTE 'SELECT .. INTO z ' i) EXECUTE 'SELECT .. ' INTO z
The function works, however if I call it from a recursive function foo, it does not (only for the first time):
(at first call it works)
NOTICE: rec=(64) CONTEXT: PL/pgSQL function "foo" line 45 at assignment NOTICE: rec=(128) CONTEXT: PL/pgSQL function "foo" line 45 at assignment NOTICE: rec=(255) CONTEXT: PL/pgSQL function "foo" line 45 at assignment
(when the function create_fp_sets is called recursively, it starts ok... )
NOTICE: rec=(75) CONTEXT: PL/pgSQL function "foo" line 45 at assignment PL/pgSQL function "foo" line 77 at EXECUTE statement
(but then...)
ERROR: type of "rec.z_val" does not match that when preparing the plan CONTEXT: PL/pgSQL function "get_distinct_values" line 16 at SQL statement PL/pgSQL function "foo" line 45 at assignment PL/pgSQL function "foo" line 77 at EXECUTE statement
********** Error **********
ERROR: type of "rec.z_val" does not match that when preparing the plan SQL state: 42804 Context: PL/pgSQL function "get_distinct_values" line 16 at SQL statement PL/pgSQL function "foo" line 45 at assignment PL/pgSQL function "foo" line 77 at EXECUTE statement
Any ideas why it does not work or how to get that array somehow?