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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
