How to fake an array of a user-defined type
От | Richard Plotkin |
---|---|
Тема | How to fake an array of a user-defined type |
Дата | |
Msg-id | 664b06e68f925ffafbeed91ea5790da5@richardplotkin.com обсуждение исходный текст |
Список | pgsql-general |
I've been reading some posts on this list, trying to figure out how to send a function an array of a user-defined type. I have figured out a way to do this. To some extent, this serves as a follow-up to "User-defined type arrays?" (http://archives.postgresql.org/pgsql-novice/2004-10/msg00132.php) 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';
В списке pgsql-general по дате отправления: