Andrew Hall wrote: > Hi, > > I was wondering whether anybody would be able to advise me on how (if it is > possible) to port some functionality from Oracle? > > This is just an example - in Oracle, I am able to do the following > > -- > -- Create a data type which replicates the data structure of a single user in > my application. > -- I know that this can be done using PostgreSQL.
> -- Create a data type which can store many instances of a single > 'TY_APP_USER' > -- [essentially this is a table valued data type]. An instance of this data > type can be > -- created and populated by the client application [a java based one in my > case]. > -- > -- I can't find any reference to something > -- similar to this using postgreSQL. The following may not do anything interesting, but it does show arrays of composite types, which is what you are after. To prevent quoting insanity, I recommend the ARRAY[] constructor rather than array literals. You do need the explicit typecasts. Oh - and version 8.3 or higher for arrays of compound types. BEGIN; CREATE TYPE typ1 AS (i integer, t text); CREATE FUNCTION print_array(a typ1[]) RETURNS void AS $$ DECLARE m int; n int; i int; e typ1; BEGIN m := array_lower(a, 1); n := array_upper(a, 1); FOR i IN m .. n LOOP e := a[i]; RAISE NOTICE '% - %', e.i, e.t; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; SELECT print_array(ARRAY[ '(1,"abc")'::typ1, '(2,"def")'::typ1 ]); SELECT print_array(ARRAY[ '(1,"abc")', '(2,"def")' ]::typ1[]); ROLLBACK; -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql