Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search for
  Advanced Search

User-defined type arrays?



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(at)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




Home | Main Index | Thread Index

Privacy Policy | PostgreSQL Archives hosted by Command Prompt, Inc. | Designed by tinysofa
Copyright © 1996 – 2008 PostgreSQL Global Development Group