| From: | Richard Plotkin <richard(at)richardplotkin(dot)com> | 
|---|---|
| To: | pgsql-general(at)postgresql(dot)org | 
| Subject: | How to fake an array of a user-defined type | 
| Date: | 2005-02-10 14:07:23 | 
| Message-ID: | 664b06e68f925ffafbeed91ea5790da5@richardplotkin.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | 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';
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2005-02-10 14:53:49 | Re: a SELECT FOR UPDATE question | 
| Previous Message | Christopher Browne | 2005-02-10 13:33:33 | Re: Functions with more than 32 parameters |