Thread: recursive execute
Hi,
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
RAISE NOTICE 'table_name=% param_name=%', table_name, param_name;
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?
Thanks!
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
RAISE NOTICE 'table_name=% param_name=%', table_name, param_name;
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?
Thanks!