Evergreen
HomePrev   Next

Chapter 19. Schema query

Table of Contents

Tables in query
bind_variable
case_branch
datatype
expr_xbet
expr_xbind
expr_xbool
expr_xcase
expr_xcast
expr_xcol
expr_xex
expr_xfunc
expr_xin
expr_xisnull
expr_xnull
expr_xnum
expr_xop
expr_xser
expr_xstr
expr_xsubq
expression
from_relation
function_param_def
function_sig
order_by_item
query_sequence
record_column
select_item
stored_query
subfield

Contains tables designed to represent user-defined queries for reports and the like.

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

  • query.expression

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

  • query.expression
  • query.function_param_def
  • query.function_sig
  • query.record_column
  • query.subfield

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

  • query.case_branch
  • query.expression
  • query.from_relation
  • query.order_by_item
  • query.select_item
  • query.stored_query

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 parent_relation IS NOT NULL) AND (join_type IS NOT NULL) AND (on_clause IS NOT NULL))
  • relation_type CHECK type = ANY (ARRAY['RELATION'::text

Tables referencing via foreign key constraints

  • query.from_relation
  • query.record_column
  • query.stored_query

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

  • query.expression
  • query.function_param_def

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

  • action.fieldset
  • query.expression
  • query.from_relation
  • query.order_by_item
  • query.query_sequence
  • query.select_item

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
Prev   Next
Chapter 18. Schema public Home Chapter 20. Schema rating