CREATE QUEUE TABLE v14

EDB Postgres Advanced Server includes extra syntax not offered by Oracle with the CREATE QUEUE TABLE SQL command. You can use this syntax with DBMS_AQADM.

Name

CREATE QUEUE TABLE — Create a queue table.

Synopsis

Use CREATE QUEUE TABLE to define a queue table:

CREATE QUEUE TABLE <name> OF <type_name> [ ( { <option_name option_value> } [, ... ] ) ]

Possible option_name and corresponding option_value values are:

• SORT_LIST — Specify option_value as priority, enq_time.
• MULTIPLE_CONSUMERS — Specify option_value asFALSE, TRUE.
• MESSAGE_GROUPING — Specify option_value as NONE, TRANSACTIONAL.
• STORAGE_CLAUSE — Specify option_value as TABLESPACE tablespace_name, PCTFREE integer, PCTUSED integer, INITRANS integer, MAXTRANS integer, STORAGE storage_option. Values for storage_option are one or more of the following: MINEXTENTS integer, MAXEXTENTS integer, PCTINCREASE integer, INITIAL size_clause, NEXT, FREELISTS integer, OPTIMAL size_clause, BUFFER_POOL {KEEP|RECYCLE|DEFAULT}.

Only the TABLESPACE option is enforced. All others are accepted for compatibility and ignored. Use the TABLESPACE clause to specify the name of a tablespace in which to create the table.

Description

CREATE QUEUE TABLE allows a superuser or a user with the aq_administrator_role privilege to create a queue table.

If the call to CREATE QUEUE TABLE includes a schema name, the queue table is created in the specified schema. If you don't provide a schema name, the queue table is created in the current schema.

The name of the queue table must be unique among queue tables in the same schema.

Parameters

name

The name (optionally schema-qualified) of the new queue table.

type_name

The name of an existing type that describes the payload of each entry in the queue table. For information about defining a type, see CREATE TYPE.

option_name option_value

The name of any options that to associate with the new queue and the corresponding value for the option. If the call to CREATE QUEUE includes duplicate option names, the server returns an error. The following values are accepted:

• SORT_LIST — Use the SORT_LIST option to control the dequeueing order of the queue. Specify the names of the columns to use, in ascending order, to sort the queue. The following combinations of enq_time and priority are possible values:

  enq_time. priority

  priority. enq_time

  priority

  enq_time

• MULTIPLE_CONSUMERS — A BOOLEAN value that indicates if a message can have more than one consumer (TRUE) or are limited to one consumer per message (FALSE).

• MESSAGE_GROUPING — Specify none to dequeue each message individually or transactional to add messages to the queue as a result of one transaction and dequeue them as a group.

• STORAGE_CLAUSE — Use STORAGE_CLAUSE to specify table attributes. Possible values are TABLESPACE tablespace_name, PCTFREE integer, PCTUSED integer, INITRANS integer, MAXTRANS integer, STORAGE storage_option. Possible values for storage_option are:

  MINEXTENTS integer

  MAXEXTENTS integer

  PCTINCREASE integer

  INITIAL size_clause

  NEXT

  FREELISTS integer

  OPTIMAL size_clause

  BUFFER_POOL {KEEP|RECYCLE|DEFAULT}

Only the TABLESPACE option is enforced. All others are accepted for compatibility and ignored. Use the TABLESPACE clause to specify the name of a tablespace in which to create the table.

Examples

You must create a user-defined type before creating a queue table. The type describes the columns and data types in the table. This command creates a type named work_order:

CREATE TYPE work_order AS (name VARCHAR2, project TEXT, completed BOOLEAN);

This command uses the work_order type to create a queue table named work_order_table:

CREATE QUEUE TABLE work_order_table OF work_order (sort_list (enq_time, priority));

See also

ALTER QUEUE TABLE, DROP QUEUE TABLE