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 по дате отправления:

Предыдущее
От: "renato.barrios"
Дата:
Сообщение: Unsubscribe
Следующее
От: John Sidney-Woollett
Дата:
Сообщение: vacuum confusion