Thread: Arrays of user-defined data types in other user-defined data types
Howdy, I'm having a little bit of trouble making arrays of a data type I'm defining into another data type I'm defining. What I'm trying to do is this: create type MYSCHEMA.T_QUESTION_RESULT as ( IND_QUESTION NUMERIC, N_ANSWER NUMERIC ); create type MYSCHEMA.T_QUESTION_RESULT_LIST as ( col1 MYSCHMA.T_QUESTION_RESULT[] ); Truth be told, I'm actually trying to port this from some definitions I have from Oracle which look like this: create or replace type MYSCHEMA.T_QUESTION_RESULT as object ( IND_QUESTION number, N_ANSWER number ); create or replace type MYSCHEMA.T_QUESTION_RESULT_LIST as table of T_QUESTION_RESULT; As you can see, in Oracle, the T_QUESTION_RESULT_LIST was actually of type table (and T_QUESTION_RESULT was of type object...) I'm basically going under the assumption that I can duplicate type object with just type in PostgreSql and type table with type and with the ONE element in the type being an array. If I'm way off base, could you let me know? Thanks! -James DeMond
James DeMond <demond@cs.dal.ca> writes: > What I'm trying to do is this: > > create type MYSCHEMA.T_QUESTION_RESULT as ( > IND_QUESTION NUMERIC, > N_ANSWER NUMERIC > ); > > create type MYSCHEMA.T_QUESTION_RESULT_LIST as ( > col1 MYSCHMA.T_QUESTION_RESULT[] > ); We don't support arrays of composite types at the moment. > Truth be told, I'm actually trying to port this from some definitions I > have from Oracle which look like this: > > create or replace type MYSCHEMA.T_QUESTION_RESULT as object ( > IND_QUESTION number, > N_ANSWER number > ); > > create or replace type MYSCHEMA.T_QUESTION_RESULT_LIST as table of > T_QUESTION_RESULT; I'm not sure what Oracle thinks the latter construct really means? Perhaps you shouldn't be trying to map it into an array. regards, tom lane
Well, from what I have read, oracle has 3 types that can be defined: objects, tables and views (possibly more?) I'm basically working under the assumption that > > create or replace type MYSCHEMA.T_QUESTION_RESULT as object ( > > IND_QUESTION number, > > N_ANSWER number > > ); is the same as > > create type MYSCHEMA.T_QUESTION_RESULT as ( > > IND_QUESTION NUMERIC, > > N_ANSWER NUMERIC > > ); meaning that types in postgresql map directly as type object in Oracle. Now, Oracle basically defines type table as an indexed one column "table", with that column being of some defined (or user) type (... as table of type number... etc). I really have no clue how to map that functionality to postgresql. I'm taking a shot that I can do something similar by defining a type with one item in it where that item is an array of some type. If anyone has a better idea, I'd be very interested in hearing it. Thanks for the reply though! It certainly is helping me understand a few things. -James DeMond