Обсуждение: recursive execute

Rastislav Hudak wrote:
> 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
[...]
> 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;

[...]

> The function works, however if I call it from a recursive
> function foo, it does not (only for the first time):
>
[...]
>
> (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
>
> Any ideas why it does not work or how to get that array somehow?

I would say that whenever you enter the loop, there is a new definition of "rec",
even if it always is a record with a single integer element.
But the INSERT statement is prepared only once and remembers the original definition.

I'm not sure about that, though.

You can avoid all these problems with this simpler definition:

CREATE OR REPLACE FUNCTION get_distinct_values(table_name text, param_name text)
  RETURNS integer[] AS
$BODY$
DECLARE
  _values integer[];
BEGIN
  EXECUTE 'SELECT ARRAY(SELECT DISTINCT CAST (' || param_name
          || ' AS integer) FROM ' || table_name || ' WHERE '
          || param_name || ' IS NOT NULL)'
  INTO _values;

  RETURN _values;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

Yours,
Laurenz Albe