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.rastis...@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!
>
>
