The general idea is to have a user-defined type paired with a user-defined cast and user-defined cast conversion function. The typecasting will change type text[] to your user-defined type, and since type text[][] is allowed, you can pass text[][] to a function and then typecast text[1]..text[n] as your user-defined type.
Hope this saves someone some time.
First, assume a created type
CREATE TYPE mytype AS (
attribute1 text,
attribute2 integer
);Then, assume a failed function (which won't work because mytype[] will not be accepted as a function parameter)
CREATE OR REPLACE FUNCTION myfunc(mytype[]) RETURNS null AS $$
DECLARE
mytype_array ALIAS FOR $1
BEGIN
RETURN null;
END;
$$ LANGUAGE 'plpgsql';Now add the following:
CREATE OR REPLACE FUNCTION to_mytype(text[]) RETURNS mytype AS
DECLARE
input ALIAS FOR $1
result mytype; BEGIN
--create a row using same types that are assigned in mytype
result = ROW(input[1]::text, input[2]::integer);
RETURN result;
END;
$$ LANGUAGE 'plpgsql';CREATE CAST (text[] as mytype) WITH FUNCTION to_mytype(text[]);
Now, change your function to the following, where text[][] is an array of mytype's that will initially be parsed as text's (so type text[][] is what you'll pass, and type mytype[] is what you'll end up dealing with. Treat each mytype as a text[])
CREATE OR REPLACE FUNCTION myfunc(text[][]) RETURNS null AS $$
DECLARE
mytype_array ALIAS FOR $1;
mytype_element mytype;
arr_u int;
arr_l int;BEGIN
--you should loop through all array-type elements of your input array (text[][]) to pull them all out as mytype's
--and then, once you've pulled them out as mytype's, deal with them immediately (you can't convert them and array_append into mytype[])
SELECT INTO arr_u array_upper(mytype_array);
SELECT INTO arr_l array_lower(mytype_array);
FOR i IN arr_l..arr_u LOOP
SELECT INTO mytype_element
CAST(mytype_array[i] AS mytype);
--anything else you want to do
END LOOP;
END;
$$ LANGUAGE 'plpgsql';
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
