Thread: Anyarray with mixed data types inside of a PL/PLSQL function parameter

Hello,

I am trying to solve the following PL/PGSQL problem now for some days and
can't get any solution :-(

I want to create a function receiving an array (anyarray) as a parameter.

The main aim of this function is to creat some INSERT-commandos depending on
the multidimension array.
That part is no problem, I think.

My Problem now is how to submit those different parameters.

In brief I want to submit an array of this array:

column_name (eg. my_row)
column_data_typ (eg. integer)
is_null (eg. false)
the_value (eg. 1 or 'Hello' or maybe 6.842 and so on - different data
types!)

When I try to submit the following multidimensional array to my
test-function:

select test(ARRAY[['my_row'::text, 'mytext'::text, false::boolean, 'thats my
value'::text],['my_row2'::text, 'integer'::text, false::boolean,
3::integer]])

I get the following error:

ERROR:  ARRAY types text and boolean cannot be matched

The following is my testing function I am planning to expand later :-)

CREATE OR REPLACE FUNCTION test(insert_values anyarray) RETURNS text AS $$
DECLARE
    give_back text;
BEGIN
    give_back := '';
       IF array_lower(insert_values,1) is not null THEN
           FOR i IN
array_lower(insert_values,1)..array_upper(insert_values,1) LOOP
                 -- the following is only for testing:
                 give_back := give_back || ' ' || insert_values[i][1];
           END LOOP;
       END IF;
    RETURN give_back;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;

It is working with this array I created to test my function test(anyarray):

select test(ARRAY[['my_row'::text, 'text'::text],['my_row2'::text,
'text'::text]])

But I can't mix up the data types within the array.

I tried an other way to solve my problem: create an own data type to use in
my function test:

CREATE TYPE data_transfer AS (
    column_name text,
    column_data_type text,
    is_null boolean,
    the_value anyelement
);

But I get the following error message:

ERROR:  column "the_value" has pseudo-type anyelement

How is it possible to submit mixed data types inside of an array?

Thank you very much for your help.

Any hint or tip will be appreciated.

Christian

"Christian Hofmann" <christian.hofmann@gmx.de> writes:
> select test(ARRAY[['my_row'::text, 'mytext'::text, false::boolean, 'thats my
> value'::text],['my_row2'::text, 'integer'::text, false::boolean,
> 3::integer]])

You can't have an "array" whose elements are of different datatypes.

            regards, tom lane