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

From: "Christian Hofmann" <christian(dot)hofmann(at)gmx(dot)de>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Anyarray with mixed data types inside of a PL/PLSQL function parameter
Date: 2005-03-01 14:54:20
Message-ID: 20050301145326.BB157566A7@svr1.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Keith Worthington 2005-03-01 17:06:34 Re: Import csv file into multiple tables in Postgres
Previous Message Todd Lewis 2005-03-01 09:14:25 Re: Import csv file into multiple tables in Postgres