Anyarray with mixed data types inside of a PL/PLSQL function parameter
От | Christian Hofmann |
---|---|
Тема | Anyarray with mixed data types inside of a PL/PLSQL function parameter |
Дата | |
Msg-id | 20050301145326.BB157566A7@svr1.postgresql.org обсуждение исходный текст |
Ответы |
Re: Anyarray with mixed data types inside of a PL/PLSQL function parameter
|
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления: