Message-ID: <4AE2EC83.6060908@archonet.com>
Date: Sat, 24 Oct 2009 13:01:07 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Andrew Hall <andrewah@hotmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Table Valued Parameters
References: <COL122-W14509AEC755F5710A3CA19CDBD0@phx.gbl>
In-Reply-To: <COL122-W14509AEC755F5710A3CA19CDBD0@phx.gbl>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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