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!
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
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!
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