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

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

2009/6/3 Rastislav Hudak <hudak(dot)rastislav(at)gmail(dot)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