[GENERAL] recursive execute

[Rastislav Hudak]

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!