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