Ok mea maxima culpa I forgot to add an important fact:
the table I'm putting to get_distinct_values(..) in the recursive call is a table that has just been created in the caller function (by EXECUTE 'CREATE TABLE ' || table_name_new || '...). In the first run, the get_distinct_values(..) obtains a normal table.
If the table would not exist at all at the time of recursive call I would understand my fault. But the first row seems to be returned, so I assume the EXECUTE 'CREATE TABLE '.. already created the table..
Thanks for any ideas..
RH
2009/6/3 Rastislav Hudak
<hudak.rastislav@gmail.com> 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!