In Firebird How to create a two dimensional char array domain

user_5492581 0
Viewed 140

How do I create a two dimensional char array domain eg: value

['ABC-12121212','1']
['ABC-12323233','2']

and and what is the insert/select/update statement if i use this domain in a table field or procedure input.

Is it CREATE DOMAIN TESTARRAY AS CHAR(14) [500:2];?

firebird
1 Answers

I would highly recommend to avoid arrays in Firebird. They are largely a holdover from pre-SQL features of InterBase, and are barely usable from the SQL language.

However, for the syntax of declaring an array domain, see the documentation on domains:

CREATE DOMAIN name [AS] <datatype>
  [DEFAULT {<literal> | NULL | <context_var>}]
  [NOT NULL] [CHECK (<dom_condition>)]
  [COLLATE collation_name]

<datatype> ::=
    {SMALLINT | INTEGER | BIGINT} [<array_dim>]
  | {FLOAT | DOUBLE PRECISION} [<array_dim>]
  | {DATE | TIME | TIMESTAMP} [<array_dim>]
  | {DECIMAL | NUMERIC} [(precision [, scale])] [<array_dim>]
  | {{CHAR | CHARACTER} [VARYING] | VARCHAR} [(size)]
    [<array_dim>] [CHARACTER SET charset_name]
  | {NCHAR | NATIONAL {CHARACTER | CHAR}} [VARYING]
    [(size)] [<array_dim>]
  | BLOB [SUB_TYPE {subtype_num | subtype_name}]
    [SEGMENT SIZE seglen] [CHARACTER SET charset_name]
  | BLOB [(seglen [, subtype_num])]

<array_dim> ::= '[' [m:]n [,[m:]n ...] ']'

The m:n refers to the lower and upper bounds of the array, so 500:2 would mean an array with a lower bound of 500 and an upper bound of 2, which of course doesn't make sense. If you want multi-dimensional arrays, then separate bounds by a comma (,). See also the documentation on the array type.

In other words, use:

CREATE DOMAIN TESTARRAY AS CHAR(14) [500,2];
user_4668620
Related