Greetings.

I'm in the process of porting an existing Oracle pl/SQL implementation
to Postgres and plpgsql. My schema, constraint, index, and type
conversion all went surprisingly smooth. I've now begun to shift my
existing pl/SQL implementations to plpgsql and haven't yet figured out
how to properly create pgplsql function parameters of user-defined type
arrays. In hunting down this issue, I realize that I do not understand
how to correctly create user-defined type arrays in any context, not
just within stored function signatures.

I believe this is possible, as the PostGres 7.4 documentation at
http://www.postgresql.org/docs/7.4/static/arrays.html says "Arrays of
any built-in type or user-defined type can be created."

Below is an example of me first creating a table containing a column
using a built-in type array. I then create a user-defined type, and
then receive an error when trying to create a table containing a column
using a user-defined type array:

eric@cure> psql eric0
Welcome to psql 7.4.3, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

eric0=# create table foo ( bar varchar[] );
CREATE TABLE
eric0=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------------------+-----------
bar | character varying[] |

eric0=# create type baz as ( garply integer );
CREATE TYPE
eric0=# \d baz
Composite type "public.baz"
Column | Type
--------+---------
garply | integer

eric0=# create table waldo ( qux baz[] );
ERROR: type "baz[]" does not exist
eric0=# \q

I'm hoping someone can clue me in as to the correct syntax for what I'm
attempting to do, or general guidance.

Thanks in advance.

-- Eric Bieschke


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Eric Bieschke <eric (AT) savagebeast (DOT) com> writes:
It should probably say "any user-defined scalar type". We don't currently
support arrays of composite types. I think there might be now be
enough infrastructure in place that it wouldn't be too hard to add that
feature, but it surely won't be available before 8.1.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match