Custom types and arrays
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
Hi,<br>
<br>
Does anyone know how to declare arrays of custom types? The
documentation says:<br>
<br>
<blockquote>As discussed earlier, PostgreSQL fully supports arrays of
base types. Additionally, PostgreSQL supports arrays of user-defined
types as well. When you define a type, PostgreSQL automatically
provides support for arrays of that type. For historical reasons, the
array type has the same name as the user-defined type with the
underscore character _ prepended.<br>
</blockquote>
When I run the following code:<br>
<br>
CREATE TYPE TEST_TYPE AS (<br>
ID INTEGER,<br>
CODE VARCHAR(40)<br>
);<br>
<br>
CREATE OR REPLACE FUNCTION Test1( ) RETURNS TEST_TYPE[20] AS'<br>
DECLARE<br>
aoTesttype TEST_TYPE[20];<br>
BEGIN<br>
RETURN aoTestType;<br>
END;' LANGUAGE 'plpgsql';<br>
<br>
CREATE OR REPLACE FUNCTION Test2( ) RETURNS _TEST_TYPE AS'<br>
DECLARE<br>
aoTesttype _TEST_TYPE;<br>
BEGIN<br>
RETURN aoTestType;<br>
END;' LANGUAGE 'plpgsql';<br>
<br>
I get:<br>
psql:Supplements.sql:34: ERROR: Type "test_type[]" does not exist<br>
psql:Supplements.sql:41: ERROR: Type "_test_type" does not exist<br>
<br>
Can anyone help?<br>
<br>
Thanks in advance.<br>
<br>
Matthew<br>
</body>
</html>
"mlunnon @ RWA" <mlunnon@rwa-net.co.uk> writes:
I get:<br>
psql:Supplements.sql:34: ERROR: Type "test_type[]" does not exist<br>
psql:Supplements.sql:41: ERROR: Type "_test_type" does not exist<br>
It works for me ... are you sure it's not something silly like mistyping
the type name, or adding/omitting double quotes?
regards, tom lane
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
Thanks for your reply tom. I'm sure it is not a mistype, I included the
definition that I used in my script. Perhaps it is something to do with
the cygwin installation of Postgres that I have i will try it on a
Linux box.<br>
<br>
Cheers<br>
Matthew<br>
<br>
Tom Lane wrote:<br>
<blockquote type="cite" cite="mid19718.1067633975@sss.pgh.pa.us">
<pre wrap="">"mlunnon @ RWA" <a class="moz-txt-link-rfc2396E" href="mailto:mlunnon@rwa-net.co.uk"><mlunnon@rwa-net.co.uk></a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">I get:<br>
psql:Supplements.sql:34: ERROR:&nbsp; Type "test_type[]" does not exist<br>
psql:Supplements.sql:41: ERROR:&nbsp; Type "_test_type" does not exist<br>
</pre>
</blockquote>
<pre wrap=""><!---->
It works for me ... are you sure it's not something silly like mistyping
the type name, or adding/omitting double quotes?
regards, tom lane
_____________________________________________________________________
This e-mail has been scanned for viruses by MCI's Internet Managed Scanning Services - powered by MessageLabs. For further information visit <a class="moz-txt-link-freetext" href="http://www.mci.com">http://www.mci.com</a>
</pre>
</blockquote>
</body>
</html>
mlunnon @ RWA wrote:
Thanks for your reply tom. I'm sure it is not a mistype, I included the
definition that I used in my script. Perhaps it is something to do with the
cygwin installation of Postgres that I have i will try it on a Linux box.
The type you were referring to is a composite type:
CREATE TYPE TEST_TYPE AS (ID INTEGER, CODE VARCHAR(40));
The documentation wording could be improved (perhaps it should say
"user-defined *base* types?), but there is no support for arrays of
composite types -- or at least if there is, I've never seen it. I know
for sure that creation of a user defined composite type will not create
a corresponding array type.
Joe