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  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: "Todd Lewis"
Дата:
Сообщение: Re: Import csv file into multiple tables in Postgres
Следующее
От: Keith Worthington
Дата:
Сообщение: Re: Import csv file into multiple tables in Postgres