Chapter 19. Schema query

Table of Contents

Tables in query

Below are the tables, views and functions for query

Tables in query

bind_variable

  • name text PRIMARY KEY
  • type text NOT NULL
  • description text NOT NULL
  • default_value text
  • label text NOT NULL

Constraints on bind_variable

  • bind_variable_type CHECK type = ANY (ARRAY['string'::text

Tables referencing via foreign key constraints

case_branch

  • id serial PRIMARY KEY
  • parent_expr integer UNIQUE #1 NOT NULL REFERENCES query.table.expression
  • seq_no integer UNIQUE #1 NOT NULL
  • condition integer REFERENCES query.table.expression
  • result integer NOT NULL REFERENCES query.table.expression

datatype

  • id serial PRIMARY KEY
  • datatype_name text UNIQUE NOT NULL
  • is_numeric boolean NOT NULL DEFAULT false
  • is_composite boolean NOT NULL DEFAULT false

Constraints on datatype

  • qdt_comp_not_num CHECK

Tables referencing via foreign key constraints

expr_xbet

  • id integer
  • parenthesize boolean
  • parent_expr integer
  • seq_no integer
  • left_operand integer
  • negate boolean

expr_xbind

  • id integer
  • parenthesize boolean
  • parent_expr integer
  • seq_no integer
  • bind_variable text

expr_xbool

  • id integer
  • parenthesize boolean
  • parent_expr integer
  • seq_no integer
  • literal text
  • negate boolean

expr_xcase

  • id integer
  • parenthesize boolean
  • parent_expr integer
  • seq_no integer
  • left_operand integer
  • negate boolean

expr_xcast

  • id integer
  • parenthesize boolean
  • parent_expr integer
  • seq_no integer
  • left_operand integer
  • cast_type integer
  • negate boolean

expr_xcol

  • id integer
  • parenthesize boolean
  • parent_expr integer
  • seq_no integer
  • table_alias text
  • column_name text
  • negate boolean

expr_xex

  • id integer
  • parenthesize boolean
  • parent_expr integer
  • seq_no integer
  • subquery integer
  • negate boolean

expr_xfunc

  • id integer
  • parenthesize boolean
  • parent_expr integer
  • seq_no integer
  • column_name text
  • function_id integer
  • negate boolean

expr_xin

  • id integer
  • parenthesize boolean
  • parent_expr integer
  • seq_no integer
  • left_operand integer
  • subquery integer
  • negate boolean

expr_xisnull

  • id integer
  • parenthesize boolean
  • parent_expr integer
  • seq_no integer
  • left_operand integer
  • negate boolean

expr_xnull

  • id integer
  • parenthesize boolean
  • parent_expr integer
  • seq_no integer
  • negate boolean

expr_xnum

  • id integer
  • parenthesize boolean
  • parent_expr integer
  • seq_no integer
  • literal text

expr_xop

  • id integer
  • parenthesize boolean
  • parent_expr integer
  • seq_no integer
  • left_operand integer
  • operator text
  • right_operand integer
  • negate boolean

expr_xser

  • id integer
  • parenthesize boolean
  • parent_expr integer
  • seq_no integer
  • operator text
  • negate boolean

expr_xstr

  • id integer
  • parenthesize boolean
  • parent_expr integer
  • seq_no integer
  • literal text

expr_xsubq

  • id integer
  • parenthesize boolean
  • parent_expr integer
  • seq_no integer
  • subquery integer
  • negate boolean

expression

  • id serial PRIMARY KEY
  • type text NOT NULL
  • parenthesize boolean NOT NULL DEFAULT false
  • parent_expr integer REFERENCES query.table.expression
  • seq_no integer NOT NULL DEFAULT 1
  • literal text
  • table_alias text
  • column_name text
  • left_operand integer REFERENCES query.table.expression
  • operator text
  • right_operand integer REFERENCES query.table.expression
  • function_id integer REFERENCES query.table.function-sig
  • subquery integer REFERENCES query.table.stored-query
  • cast_type integer REFERENCES query.table.datatype
  • negate boolean NOT NULL DEFAULT false
  • bind_variable text REFERENCES query.table.bind-variable

Constraints on expression

  • expression_type CHECK type = ANY (ARRAY['xbet'::text

Tables referencing via foreign key constraints

from_relation

  • id serial PRIMARY KEY
  • type text NOT NULL
  • table_name text
  • class_name text
  • subquery integer REFERENCES query.table.stored-query
  • function_call integer REFERENCES query.table.expression
  • table_alias text
  • parent_relation integer REFERENCES query.table.from-relation
  • seq_no integer NOT NULL DEFAULT 1
  • join_type text
  • on_clause integer REFERENCES query.table.expression

Constraints on from_relation

  • good_join_type CHECK
  • join_or_core CHECK (((((parent_relation IS NULL) AND (join_type IS NULL)) AND (on_clause IS NULL)) OR
  • relation_type CHECK type = ANY (ARRAY['RELATION'::text

Tables referencing via foreign key constraints

function_param_def

  • id serial PRIMARY KEY
  • function_id integer UNIQUE #1 NOT NULL REFERENCES query.table.function-sig
  • seq_no integer UNIQUE #1 NOT NULL
  • datatype integer NOT NULL REFERENCES query.table.datatype

Constraints on function_param_def

  • qfpd_pos_seq_no CHECK seq_no > 0

function_sig

  • id serial PRIMARY KEY
  • function_name text NOT NULL
  • return_type integer REFERENCES query.table.datatype
  • is_aggregate boolean NOT NULL DEFAULT false

Constraints on function_sig

  • qfd_rtn_or_aggr CHECK

Indexes on function_sig

  • query_function_sig_name_idx function_name

Tables referencing via foreign key constraints

order_by_item

  • id serial PRIMARY KEY
  • stored_query integer UNIQUE #1 NOT NULL REFERENCES query.table.stored-query
  • seq_no integer UNIQUE #1 NOT NULL
  • expression integer NOT NULL REFERENCES query.table.expression

query_sequence

  • id serial PRIMARY KEY
  • parent_query integer UNIQUE #1 NOT NULL REFERENCES query.table.stored-query
  • seq_no integer UNIQUE #1 NOT NULL
  • child_query integer NOT NULL REFERENCES query.table.stored-query

record_column

  • id serial PRIMARY KEY
  • from_relation integer UNIQUE #1 NOT NULL REFERENCES query.table.from-relation
  • seq_no integer UNIQUE #1 NOT NULL
  • column_name text NOT NULL
  • column_type integer NOT NULL REFERENCES query.table.datatype

select_item

  • id serial PRIMARY KEY
  • stored_query integer UNIQUE #1 NOT NULL REFERENCES query.table.stored-query
  • seq_no integer UNIQUE #1 NOT NULL
  • expression integer NOT NULL REFERENCES query.table.expression
  • column_alias text
  • grouped_by boolean NOT NULL DEFAULT false

stored_query

  • id serial PRIMARY KEY
  • type text NOT NULL
  • use_all boolean NOT NULL DEFAULT false
  • use_distinct boolean NOT NULL DEFAULT false
  • from_clause integer REFERENCES query.table.from-relation
  • where_clause integer REFERENCES query.table.expression
  • having_clause integer REFERENCES query.table.expression
  • limit_count integer REFERENCES query.table.expression
  • offset_count integer REFERENCES query.table.expression

Constraints on stored_query

  • query_type CHECK type = ANY (ARRAY['SELECT'::text

Tables referencing via foreign key constraints

subfield

  • id serial PRIMARY KEY
  • composite_type integer UNIQUE #1 NOT NULL REFERENCES query.table.datatype
  • seq_no integer UNIQUE #1 NOT NULL
  • subfield_type integer NOT NULL REFERENCES query.table.datatype

Constraints on subfield

  • qsf_pos_seq_no CHECK seq_no > 0