Postgres Pro
Downloads
Home   >   mailing lists

Thread: Custom types and arrays

Custom types and arrays

From
"mlunnon @ RWA"
Date:
Hi,<br /><br /> Does anyone know how to declare arrays of custom types?  The documentation says:<br /><br
/><blockquote>Asdiscussed earlier, PostgreSQL fully supports arrays of base types. Additionally, PostgreSQL supports
arraysof user-defined types as well. When you define a type, PostgreSQL automatically provides support for arrays of
thattype. 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
FUNCTIONTest1( ) RETURNS TEST_TYPE[20] AS'<br /> DECLARE<br />     aoTesttype TEST_TYPE[20];<br /> BEGIN<br />    
RETURNaoTestType;<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 />

Re: Custom types and arrays

From
Tom Lane
Date:
"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

Re: Custom types and arrays

From
"mlunnon @ RWA"
Date:
Thanks for your reply tom. I'm sure it is not a mistype, I included the definition that I used in my script. Perhaps
itis 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 cite="mid19718.1067633975@sss.pgh.pa.us"
type="cite"><prewrap="">"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="">Iget:<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
furtherinformation visit <a class="moz-txt-link-freetext" href="http://www.mci.com">http://www.mci.com</a>
</pre></blockquote>

Re: Custom types and arrays

From
Joe Conway
Date:
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