D2_99_PSTGMG.TPW

#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#! File Name:   D2PSTGREMG.tpw
#! Purpose:     DCT to POSTGRESQL creation script
#! Author:      Copyright © 1999-2999 by Roberto Artigas Jr
#!              All rights reserved world wide.
#! Changes:
#! 2008.08.17   Michael Gould
#!      Added support for UUID data types and default processing.  This is done in Group
#!      #GROUP(%PGSQLField) in D2PSTGRE.tpw
#! 2008.08.12   Roberto Artigas
#!  *   Changed DELATE to DELETE (cascade changes).
#! 2008.08.11   Robert Artigas
#!  *   Added additional variables and #PROMPTs so that this template
#!      could use the common routines in the generic PostgreSQL template.
#!  *   Added the same prefix options as to the generic template.
#! 2008.07.18   Michael Gould
#!  *   Added the ON DELETE /ON UPDATE processing for building relations
#!  *   Added the ability to Defer the constraint check until the transaction ends
#! 2008.06.29   Roberto Artigas
#!  *   Enhanced the GRANT options. Added GRANT ALL checkbox option.
#! 2008.06.25   Michael Gould
#!  *   Added code to automatically create triggers to update createdatetime, create user
#!      editdatetime and edituser.  The user can control the column names on a new tab
#!      as well they can turn off this feature by table for each pair (create or edit)
#! 2008.06.24   Roberto Artigas
#!  *   Additional search path SQL statements when using a schema name.
#!  *   Implemented CHECK based on dictionary validations. INFILE not done at this time.
#!  *   Change order (where processed) the RASQLCHECK option. Now overides dictionary.
#! 2008.06.20   Roverto Artigas
#!  *   Fixed RASQLCHECK option to handle IN ('','','','').
#! 2008.03.22   Roberto Artigas
#!  *   Corrected small template error in the unique index lowercase logic.
#!  *   Disabled posible errors in the automatic generation of serial fields.
#! 2008.03.14   Roberto Artigas
#!  *   Added function creation for descriptions.
#!  *   Added RASQLCHECK option to template at the field level
#! 2008.02.23   Michael Gould
#!  *   Added ability to create elements in lower case
#! 2007.12.28   Roberto Artigas
#!  *   Added 'COMMENT ON' to field.
#! 2007.12.27   Roberto Artigas
#!  *   Added 'COMMENT ON' to table, sequence, primary key, and index.
#! 2007.12.26   Roberto Artigas
#!  *   Correct index names to obey the IGNORE reserved words option.
#! 2007.12.05   Dave Harms
#!  *   Added file prefix to index names
#! 2007.11.25   Roberto Artigas
#!  *   Logic changes to make sure the scripts are generated in file name sequence
#!  *   Slight logic changes in the CASE statement for INLIST validation.
#! 2007.10.31   Roberto Artigas
#!  *   Added ignore NOT NULL for all fields to avoid Clarion pre-insert failures of the
#!      blank record on the SQL backend. The validation will be still generated in the program.
#! 2007.10.27   Roberto Artigas
#!  *   Added ignore NOT NULL for all keys lookup and set up.
#!  *   Added mechanism for the 'inteligent' field default of backend specific values
#!      by way of the third field option in RASQL having a 'YES' value or an actual value.
#! 2007.10.24   Roberto Artigas
#!  *   Made "Making Strings Into VarChar" an option. Default to NO.
#! 2007.10.21   Roberto Artigas
#!  *   Corrected relation to ALIAS by replacing the ALIAS'ed name with the real file name
#!      since ALIAS'es do not exit in the database backend. They are a clarion construction.
#! 2007.10.07   Roberto Artigas
#!  *   Finish up validations on the backend for BOOLEAN and INLIST.
#!  *   Added CHECK option and implemented.
#! 2007.10.03   Roberto Artigas
#!  *   Begin adding the validation functions for the backend.
#! 2007.09.28   Roberto Artigas
#!  *   Added logic and types for TIME WITHOUT TIME ZONE which Clarion implements.
#! 2007.09.20   Roberto Artigas
#!  *   Added logic for file option EMULATEAUTONUMKEY and field option ISIDENTITY.
#!  *   Added option for SERIAL or BIGSERIAL depending on dictionary option ISIDENTITY.
#! 2007.07.17   Roberto Artigas
#!  *   Added ignore reserved words option and schema name option.
#!  *   Added modify name #GROUPs to simplify and localize name manipulation logic
#! 2007.07.16   Roberto Artigas
#!  *   Added PosgreSQL data types to the template
#! 2007.05.08   Douglas E. Johnson <douglas.e.johnson@colorado.edu>
#!  *   Added External Names option to the template.
#! 2003.02.12   Gunnar Sønsteby <gunnar@nor-net.no>
#!  *   Added   TAB Picture.
#!              Choice for converting PICTURE-fields to char(n).
#! 2003.02.12   Gunnar Sønsteby <gunnar@nor-net.no>
#!  *   Added   TAB VarChar.
#!              Lets user choose how many characters before turning string to varchar(n).
#! 2003.02.10   Gunnar Sønsteby <gunnar@nor-net.no>
#!  *   Changed ALTER TABLE filename ADD CONSTRAINT keyname UNIQUE [KEY]|INDEX (field,...)
#!              to
#!              CREATE [UNIQUE] INDEX keyname ON filename (field,...)
#! 2003.01.18   Gunnar Sønsteby <gunnar@nor-net.no>
#!  *   Changed BYTE, UTINYINT, UINT1 from TINYINT to SMALLINT as TINYINT does not exist in Postgres.
#! 2002.05.03   Roberto Artigas Jr
#!  *   Created skeleton for the POSTGRESQL variant.
#!---------------------------------------------------------------------
#UTILITY(Dct2PostgreSQL_MichaelGould, '2016.07.17: Dictionary to POSTGRESQL script')
#!---------------------------------------------------------------------
#BOXED('Hidden Variables'),HIDE
  #PROMPT('DictName: '    ,@S40 ),%DictName
  #PROMPT('DictPath: '    ,@S80 ),%DictPath
  #PROMPT('nLoc1: '       ,@N4  ),%nLoc1
  #PROMPT('nLoc2: '       ,@N4  ),%nLoc2
  #PROMPT('OutTableName: ',@S120),%OutTableName
  #PROMPT('OutGrantName: ',@S120),%OutGrantName
  #PROMPT('OutIndexName: ',@S120),%OutIndexName
  #PROMPT('OutRelatName: ',@S120),%OutRelatName
  #PROMPT('OutValidName: ',@S120),%OutValidName
  #PROMPT('OutDatesName: ',@S120),%OutDatesName
#ENDBOXED
#!
#SHEET,HSCROLL
 #TAB('General')
  #BOXED('')
   #DISPLAY('The POSTGRESQL Script Creator.')
   #DISPLAY('Copyright 2002-2008 © by Roberto Artigas Jr.')
   #DISPLAY('All Rights Reserved World Wide.')
   #DISPLAY('')
   #PROMPT('Directory:'  ,@S80 ),%SQLDirectory,AT(52,,130)
   #DISPLAY('')
   #PROMPT('Generate CREATE?'   ,CHECK),%SQLTable,DEFAULT(%TRUE),AT(10)
   #PROMPT('CREATE Script:'     ,@S40 ),%SQLTableName,AT(82,,100)
   #DISPLAY('')
   #PROMPT('Generate GRANT? '   ,CHECK),%SQLGrant,DEFAULT(%FALSE),AT(10)
   #PROMPT('GRANT Script:'      ,@S40 ),%SQLGrantName,AT(82,,100)
   #DISPLAY('')
   #PROMPT('Generate KEYS?'     ,CHECK),%SQLIndex,DEFAULT(%TRUE),AT(10)
   #PROMPT('KEYS Script:'       ,@S40 ),%SQLIndexName,AT(82,,100)
   #DISPLAY('')
   #PROMPT('Generate RELATION?' ,CHECK),%SQLRelat,DEFAULT(%FALSE),AT(10)
   #PROMPT('RELATION Script:'   ,@S40 ),%SQLRelatName,AT(82,,100)
   #ENABLE(%SQLRelat)
    #PROMPT('Allow for Deferrable Constraints?',CHECK),%SQLRelatDefer,DEFAULT(%FALSE),AT(20)
   #ENDENABLE
   #DISPLAY('')
   #PROMPT('Generate VALIDATE?' ,CHECK),%SQLValid,DEFAULT(%FALSE),AT(10)
   #ENABLE(%SQLValid)
     #PROMPT('Generate CHECK?' ,CHECK),%SQLValidCheck,DEFAULT(%FALSE),AT(20)
   #ENDENABLE
   #PROMPT('VALIDATE Script:'   ,@S40 ),%SQLValidName,AT(82,,100)
   #DISPLAY('')
   #PROMPT('Generate Date Triggers?',CHECK),%SQLDateValid,DEFAULT(%FALSE),AT(10)
   #ENABLE(%SQLDateValid)
    #PROMPT('Trigger Script:'   ,@S40),%SQLDatesName,AT(82,,100)
   #ENDENABLE
  #ENDBOXED
 #ENDTAB
 #!-----------------------------------------------------------!
 #! RA.2008.06.29 - Added enxhancements to the GRANT options. !
 #!-----------------------------------------------------------!
 #TAB('Grant')
   #BOXED('')
    #PROMPT('Grant ALL priviledges ', CHECK), %optGrantAll, DEFAULT(0), AT(10)
    #ENABLE(%optGrantAll)
      #PROMPT('Grant ALL user name: ', @S40), %optGrantAllUser, DEFAULT('postgres')
    #ENDENABLE
    #DISPLAY('')
    #BUTTON('User or Groups'), MULTI(%optGrant, %optGrantType & '  ' & %optGrantName & '  ' & (%optGrantSelect + %optGrantInsert + %optGrantUpdate + %optGrantDelete)), INLINE, AT(,,,180)
      #PROMPT('Type of Grant: ',OPTION), %optGrantType, AT(,,180), DEFAULT('User'), REQ
      #PROMPT('&User', RADIO)
      #PROMPT('&Group', RADIO)
      #PROMPT('User or Group Name: ', @S40 ), %optGrantName, DEFAULT('public'), REQ
      #PROMPT('Select', CHECK), %optGrantSelect, DEFAULT(1)
      #PROMPT('Insert', CHECK), %optGrantInsert, DEFAULT(1)
      #PROMPT('Update', CHECK), %optGrantUpdate, DEFAULT(1)
      #PROMPT('Delete', CHECK), %optGrantDelete, DEFAULT(1)
    #ENDBUTTON
   #ENDBOXED
 #ENDTAB
 #PREPARE
 #IF(~ITEMS(%optGrant))
   #ADD(%optGrant,'User')
 #ENDIF
 #ENDPREPARE
 #!----------------------------------------------------------------------!
 #! RA.2007.07.17 - Added ignore reserved words and schema name options. !
 #!----------------------------------------------------------------------!
 #TAB('Options')
   #BOXED('General')
     #PROMPT('Ignore NOT NULL for all keys ',CHECK),%IgnoreNotNullKeys,AT(10),DEFAULT(%TRUE)
     #PROMPT('Ignore NOT NULL for all fields ',CHECK),%IgnoreNotNullFields,AT(10),DEFAULT(%TRUE)
     #DISPLAY('')
     #PROMPT('Use SEQUENCE for auto-increment ',CHECK),%UseSerial,AT(10),DEFAULT(%TRUE)
     #PROMPT('Key fields type INT to BIGINT ', CHECK),%MakeIntBigint,AT(10),DEFAULT(%True)              #! RA.2008.07.01
     #DISPLAY('')
     #PROMPT('Ignore reserved words ',CHECK),%IgnoreReserved,AT(10),DEFAULT(%TRUE)
     #DISPLAY('')
     #PROMPT('Create table and column names in lowercase',CHECK),%UseLowerCase,AT(10),Default(%True)    #! MAG 02.21.08
     #DISPLAY('')
#!     #PROMPT('Generate OWNER for tables ',CHECK),%UseOwner,AT(10),DEFAULT(0)
#!     #ENABLE(%UseOwner)
#!       #PROMPT('Owner Name:',@S40),%OwnerName,AT(82,,100),DEFAULT('postgres')
#!     #ENDENABLE
#!     #DISPLAY('')
     #PROMPT('Use schema name ',CHECK),%UseSchemaName,AT(10),DEFAULT(%FALSE)
     #ENABLE(%UseSchemaName)
       #PROMPT('Schema Name:',@S40),%SchemaName,AT(82,,100),DEFAULT('public')
     #ENDENABLE
   #ENDBOXED
   #BOXED('Prefixes')
     #PROMPT('Prefix Options ',OPTION),%UsePrefix,DEFAULT('None')
     #PROMPT('None',RADIO),AT(15)
     #PROMPT('All',RADIO),AT(15)
     #PROMPT('Some',RADIO),AT(15)
     #ENABLE(%UsePrefix='Some')
       #PROMPT('Use file prefix for table names ',CHECK),%PrefixTables,AT(10),DEFAULT(%False)
       #PROMPT('Use file prefix for column names ',CHECK),%PrefixColumns,AT(10),DEFAULT(%False)
       #PROMPT('Use file prefix for key names ',CHECK),%PrefixKeys,AT(10),DEFAULT(%False)
       #PROMPT('Use file prefix for relation names ',CHECK),%PrefixRelations,AT(10),DEFAULT(%False)
     #ENDENABLE
   #ENDBOXED
   #BOXED('Comments')
     #PROMPT('Generate database comments ',CHECK),%CommentsDB,AT(10),DEFAULT(%True)
     #PROMPT('Generate SQL script comments ',CHECK),%CommentsSQL,AT(10),DEFAULT(%True)
   #ENDBOXED
 #ENDTAB
#!$$------------------------------------------------!
#!$$ DEJ.2007.05.08 - Use External Field Name
#!$$------------------------------------------------!
 #TAB('Field/Column')                                                                         #!$$
   #BOXED('')                                                                                 #!$$
     #DISPLAY('The default is to generate a create script that uses')                         #!$$
     #DISPLAY('any External Field Names defined in your dictionary.')                         #!$$
     #DISPLAY('In addition, Postgres will create the column names in')                        #!$$
     #DISPLAY('lowercase unless the name is generated within quotes.')                        #!$$
     #DISPLAY('')                                                                             #!$$
     #PROMPT('Generate External Name as Column',CHECK),%UseExtFieldName,AT(10),DEFAULT(0)     #!$$
     #ENABLE(%UseExtFieldName)                                                                #!$$
       #PROMPT('Generate Column in Quotes',CHECK),%UseExtFieldQuote,AT(25),DEFAULT(0)         #!$$
     #ENDENABLE                                                                               #!$$
   #ENDBOXED                                                                                  #!$$
 #ENDTAB                                                                                      #!$$
#!$$------------------------------------------------!
#!$$ End of Use External Name
#!$$------------------------------------------------!
 #TAB('Arrays')
  #BOXED('')
   #DISPLAY('There are two ways to handle arrays:')
   #DISPLAY('')
   #DISPLAY('1) You defined a group with all the fields of the array')
   #DISPLAY('inside this group. Then you put the array OVER the')
   #DISPLAY('group. If you set up your arrays in this manner, then')
   #DISPLAY('LEAVE the checkbox below OFF.')
   #DISPLAY('')
   #DISPLAY('2) You define your arrays as actual fields using no')
   #DISPLAY('groups whatsoever. If you set up your arrays this way,')
   #DISPLAY('then TURN ON the checkbox bellow.')
   #DISPLAY('')
   #DISPLAY('')
   #PROMPT('I DO NOT HAVE ARRAYS OVER GROUPS. ',CHECK),%NoArrayOver,AT(10),DEFAULT(0)
   #DISPLAY('')
   #DISPLAY('')
   #DISPLAY('')
   #DISPLAY('')
   #DISPLAY('')
  #ENDBOXED
 #ENDTAB
 #TAB('Varchars')
  #BOXED('')
   #DISPLAY('Postgres supports two primary character types.')
   #DISPLAY('  char(n)    - fixed length, blank padded')
   #DISPLAY('  varchar(n) - variable length with limit')
   #DISPLAY('')
   #DISPLAY('The storage requirement for varchar(n) is four bytes plus')
   #DISPLAY('actual string with a maximum limit of n characters.')
   #DISPLAY('So, if you excpect a string to be less than max')
   #DISPLAY('characters minus 4 bytes for more than half of the')
   #DISPLAY('records, you should probably turn it into a varchar.')
   #DISPLAY('To optimaly tune this, you have to edit the script')
   #DISPLAY('produced by the template on a field to field basis.')
   #DISPLAY('')
   #DISPLAY('')
   #PROMPT('Varchar special processing ', CHECK), %VarCharSpecial, AT(10),DEFAULT(0)
   #DISPLAY('')
   #ENABLE(%VarCharSpecial)
     #PROMPT('Varchar for strings >= : ', @N4),%VarcharLen,AT(,,30,10),DEFAULT(256)
   #ENDENABLE
   #DISPLAY('')
   #DISPLAY('')
  #ENDBOXED
 #ENDTAB
 #TAB('Picture')
  #BOXED('')
   #DISPLAY('You may convert PICTURE-fields to CHAR(n) by')
   #DISPLAY('checking this option.')
   #DISPLAY('')
   #DISPLAY('If not, the fields are showing up as ***PICTURE***')
   #DISPLAY('and you have to edit the generated script for this')
   #DISPLAY('fieldtype.')
   #DISPLAY('')
   #DISPLAY('')
   #PROMPT('Make PICTURES as CHAR(n). ',CHECK),%PicAsChar,AT(10),DEFAULT(0)
   #DISPLAY('')
   #DISPLAY('')
  #ENDBOXED
 #ENDTAB
 #TAB('Create/Edit Triggers')
  #BOXED('')
  #DISPLAY('This will help you automatically build the triggers for insert')
  #DISPLAY('or updatefor createdatetime, createuser and')
  #DISPLAY('editdatetime and edituser.')
  #DISPLAY('')
  #DISPLAY('If you do not have either of these groups in a table you')
  #DISPLAY('can turn off this processing by going to the File Options')
  #DISPLAY('Tab and entering in RASQL as the key.  If you set')
  #DISPLAY('Option3 to NO then the createdatetime and createuser')
  #DISPLAY('will not be processed and if you set option4 to NO')
  #DISPLAY('then the same for the edit process')
  #DISPLAY('')
  #ENABLE(%SQLDateValid)
      #PROMPT('Create DateTime Variable',@S40),%CreateDateTime,AT(82,,100),DEFAULT('createdatetime')
      #PROMPT('Create UserName Variable',@S40),%CreateUser,AT(82,,100),DEFAULT('createuser')
      #DISPLAY('')
      #PROMPT('Edit DateTime Variable',@S40),%EditDateTime,AT(82,,100),DEFAULT('editdatetime')
      #PROMPT('Edit UserName Variable',@S40),%EditUser,AT(82,,100),DEFAULT('edituser')
  #ENDENABLE
  #ENDBOXED
 #ENDTAB
 #TAB('UUID Processing')
   #BOXED('')
    #DISPLAY('PostGres 8.3.x now supports UUID/GUID data types.')
    #DISPLAY('')
    #DISPLAY('For this to work in these templates the contrib module')
    #DISPLAY('uuid-ossp must be installed in the database - PostGres ')
    #DISPLAY('manual - Appendix F')
    #DISPLAY('You can also set up to call a default algorithm just like a')
    #DISPLAY('autoincremented column.')
    #DISPLAY('')
    #DISPLAY('This is accomplished by using the field options in the')
    #DISPLAY('dictionary')
    #DISPLAY(',UUID will generate a UUID with no default')
    #DISPLAY('')
    #DISPLAY(',UUID1 will generate the statement')
    #DISPLAY(' UUID default uuid_generate_v1()')
    #DISPLAY('')
    #DISPLAY(',UUID2 will generate the statement')
    #DISPLAY(' UUID default uuid_generate_v1mc()')
    #DISPLAY('')
    #DISPLAY(',UUID3 will generate the statement')
    #DISPLAY(' UUID default uuid_generate_v4()')
    #DISPLAY('')
   #ENDBOXED
  #ENDTAB
#ENDSHEET
#BOXED('Hidden Variables'),HIDE
  #PROMPT('Generate script comments? ',CHECK),%DoComments,AT(10),DEFAULT(0)
  #PROMPT('Force BOOLEAN validation to [Y,N] choices? ',CHECK),%ForceBoolYN,AT(10),DEFAULT(1)
  #PROMPT('Do KEYFIELD-FILE list to create relations? ',CHECK),%DoFieldFile,AT(10),DEFAULT(0)
  #PROMPT('True Value (Y,T,1): ',@S1),%TrueValue,REQ,DEFAULT('Y')
  #PROMPT('False Value (N,F,0): ',@S1),%FalseValue,REQ,DEFAULT('N')
#ENDBOXED
#!---------------------------------------------------------------------
#PREPARE
#!
#SET(%nLoc1,INSTRING('\',%DictionaryFile,1,1))
#LOOP,WHILE(%nLoc1)
  #SET(%nLoc2,%nLoc1)
  #SET(%nLoc1,INSTRING('\',%DictionaryFile,1,%nLoc1+1))
#ENDLOOP
#SET(%DictName,UPPER(SUB(%DictionaryFile,%nLoc2+1,LEN(%DictionaryFile)-%nLoc2-4)))
#SET(%DictPath,UPPER(SUB(%DictionaryFile,1,%nLoc2)))
#!
#SET(%SQLDirectory,%DictPath)
#SET(%SQLTableName,%DictName & '_01_PG.SQL')
#SET(%SQLGrantName,%DictName & '_02_PG.SQL')
#SET(%SQLIndexName,%DictName & '_03_PG.SQL')
#SET(%SQLRelatName,%DictName & '_04_PG.SQL')
#SET(%SQLValidName,%DictName & '_05_PG.SQL')
#SET(%SQLDatesName,%DictName & '_06_PG.SQL')

#!
#ENDPREPARE
#!---------------------------------------------------------------------
#! SQL specific options
#DECLARE(%NFileOpt0)                    #! File option 0  - EMULATEAUTONUMKEY(TRUE)
#DECLARE(%NFieldOpt0)                   #! Field option 0 - IsIdentity(TRUE)
#DECLARE(%NComment)                     #! Generate comments with QUOTE'd strings
#DECLARE(%WorkIt)                       #! Temporary variable
#DECLARE(%DateTrigger)                  #! Temporary variable
#!
#DECLARE(%NFileUser)                    #! File user options [RASQL]
#DECLARE(%NFileOpt1)                    #! File option 1 [NO]
#DECLARE(%NFileOpt2)                    #! File option 2
#DECLARE(%NFileOpt3)                    #! File option 3 [Used to turn off Create Date/Time and Create User]  MAG 06.25.08
#DECLARE(%NFileOpt4)                    #! File option 4 [Used to turn off Edit Date/Time and Edit User]  MAG 06.25.08
#!
#DECLARE(%NFieldUser)                   #! Field user options [RASQL]
#DECLARE(%NFieldOpt1)                   #! Field option 1 [NO]
#DECLARE(%NFieldOpt2)                   #! Field option 2 [<backend database type override>]
#DECLARE(%NFieldOpt3)                   #! Field option 3 [<backend database default value>]
#DECLARE(%NFieldOpt4)                   #! Field option 4
#!
#DECLARE(%NKeyUser)                     #! Key user options [RASQL]
#DECLARE(%NKeyOpt1)                     #! Key option 1 [NO]
#DECLARE(%NKeyOpt2)                     #! Key option 2
#DECLARE(%NKeyOpt3)                     #! Key option 3
#DECLARE(%NKeyOpt4)                     #! Key option 4
#!
#DECLARE(%NRelationUser)                #! Relation user options [RASQL]
#DECLARE(%NRelationOpt1)                #! Relation option 1 [NO]
#DECLARE(%NRelationOpt2)                #! Relation option 2
#DECLARE(%NRelationOpt3)                #! Relation option 3
#DECLARE(%NRelationOpt4)                #! Relation option 4
#!
#DECLARE(%NFileName)                    #! File Name
#DECLARE(%NFilePrefix)                  #! File Prefix - RA.2008.07.19
#DECLARE(%NFileType)                    #! TYPE=???
#DECLARE(%NFileRowFormat)               #! ROW_FORMAT=???
#!
#DECLARE(%NFieldPKey)                   #! Field is Primary Key?
#DECLARE(%NFieldLine)                   #! Field line
#DECLARE(%NFieldName)                   #! Field Name
#DECLARE(%NFieldTSql)                   #! Field SQL type
#DECLARE(%NFieldTDef)                   #! Field SQL default
#DECLARE(%NFieldType)                   #! Field type
#DECLARE(%NFieldDef)                    #! Field default
#DECLARE(%NFieldCheck)                  #! Field CHECK(...) constraint
#DECLARE(%NFieldCheckOver)              #! Field CHECK(...) constraint override
#DECLARE(%NFieldAuto)                   #! Field AUTO_INCREMENT
#DECLARE(%NFieldNN)                     #! Field NOT NULL
#DECLARE(%NFieldSize  ,LONG)            #! Field decimal size
#DECLARE(%NFieldDec   ,LONG)            #! Field decimal places
#DECLARE(%NLeadZero   ,LONG)            #! Have a leading zero
#DECLARE(%NFNameAuto)                   #! Field Name Auto Increment
#DECLARE(%NVarcharLen ,LONG)            #! Length before turning to VARCHAR
#SET(%NVarcharLen, %VarcharLen)         #! Turn the input field to a long
#!
#DECLARE(%NKeyName)                     #! Key name
#DECLARE(%NKeyFields)                   #! Key building line
#DECLARE(%NKeyField)                    #! Key field current
#DECLARE(%NKeyField1)                   #! Key field current (from)
#DECLARE(%NKeyField2)                   #! Key field current (to)
#DECLARE(%NKeyFieldCount)               #! Number of key fields
#DECLARE(%PrimaryCount)                 #! Primary key exists
#DECLARE(%SecondaryCount)               #! Secondary key exists
#DECLARE(%AutoIncrementCount)           #! Auto-Increment key exists
#!
#DECLARE(%NRelateItem)                  #! Working item number
#DECLARE(%NRelateCount)                 #! Total items to work
#DECLARE(%NRelateComma)                 #! Number of commas
#DECLARE(%NRelateFile)                  #! Related file
#DECLARE(%NRelateField1)                #! Related origin field
#DECLARE(%NRelateField2)                #! Related relate field
#DECLARE(%RelationField1Count)          #! Count of origin file key items
#DECLARE(%RelationField2Count)          #! Count of relate file key items
#DECLARE(%RelationField1)               #! The origin file keys
#DECLARE(%RelationField2)               #! The relate file keys
#DECLARE(%RelationUpdate)               #! ON UPDATE ?
#DECLARE(%RelationDelete)               #! ON DELETE ?
#!
#!#DECLARE(%nLoc1       ,LONG)            #! Location 1
#!#DECLARE(%nLoc2       ,LONG)            #! Location 2
#DECLARE(%TheCount    ,LONG)            #! Count posible relations
#DECLARE(%CStringLen)                   #! Length of types shortened by one
#DECLARE(%LastField)                    #! Last field processed
#DECLARE(%InnerGroup)                   #! Inner group level processed
#DECLARE(%FieldComma)                   #! Number of fields - 1
#!
#DECLARE(%ChoiceLine)                   #! Build ENUM field type line
#DECLARE(%ChoiceComma)                  #! Count ENUM values - 1
#!
#DECLARE(%DocVar)                       #! Documentation Variable
#DECLARE(%TableCount, 0)                #! Count of tables
#DECLARE(%AutoCount, 0)                 #! Count of Auto-Increment Keys
#DECLARE(%PKeyCount, 0)                 #! Count of primary keys (From Keys)
#DECLARE(%PKeyOption, 0)                #! Count of primary keys (From Options)
#DECLARE(%KeyOthers, 0)                 #! Count of secondary keys
#DECLARE(%KeyCount, 0)                  #! Count of all keys combined
#DECLARE(%FieldCount, 0)                #! Count of fields
#DECLARE(%RelCount, 0)                  #! Relationship Counts
#DECLARE(%FieldTable),MULTI,UNIQUE      #! List of unique fields
#DECLARE(%FieldUnique, 0)               #! Count of unique fields
#!
#DECLARE(%TheDate)                      #! Display current date
#DECLARE(%TheTime)                      #! Display current time
#SET(%TheDate,FORMAT(TODAY(),@D010))    #! Set current date
#SET(%TheTime,FORMAT(CLOCK(),@T06))     #! Set current time
#!
#DECLARE(%DimCount          ,LONG)      #! Number of dimensions
#DECLARE(%FldArray          ,STRING)    #! Created field name from array
#DECLARE(%FD1S              ,STRING)    #! Number for dimension 1
#DECLARE(%FD2S              ,STRING)    #! Number for dimension 2
#DECLARE(%FD3S              ,STRING)    #! Number for dimension 3
#DECLARE(%FD4S              ,STRING)    #! Number for dimension 4
#DECLARE(%FD1I              ,LONG)      #! Looping in Dimension 1
#DECLARE(%FD2I              ,LONG)      #! Looping in Dimension 2
#DECLARE(%FD3I              ,LONG)      #! Looping in Dimension 3
#DECLARE(%FD4I              ,LONG)      #! Looping in Dimension 4
#DECLARE(%FD1L              ,LONG)      #! Set to %FieldDimension1
#DECLARE(%FD2L              ,LONG)      #! Set to %FieldDimension2
#DECLARE(%FD3L              ,LONG)      #! Set to %FieldDimension3
#DECLARE(%FD4L              ,LONG)      #! Set to %fieldDimension4
#!
#DECLARE(%ValTypeDCT        ,STRING)    #! Original type from dictionary
#DECLARE(%ValType           ,STRING)    #! Validation type for backend
#DECLARE(%ValLength         ,LONG)      #! String length
#DECLARE(%ValNumeric        ,LONG)      #! Length of entry box
#DECLARE(%ValDecimal        ,LONG)      #! Number of decimal places
#DECLARE(%ValTrue           ,STRING)    #! TRUE Value
#DECLARE(%ValFalse          ,STRING)    #! FALSE Value
#DECLARE(%ValI              ,LONG)      #! Index into list of values
#DECLARE(%ValList),MULTI,UNIQUE         #! List of Values
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#DECLARE(%optGrantCount     ,LONG)      #! Number of options created                #! RA.2008.06.29
#DECLARE(%optGrantTotal     ,LONG)      #! Number of options checked on             #! RA.2008.06.29
#DECLARE(%optGrantLine      ,STRING)    #! Grant option line                        #! RA.2008.06.29
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#DECLARE(%ProcFile),MULTI,UNIQUE        #! RA.2007.11.25
#FOR(%File)                             #! RA.2007.11.25
  #ADD(%ProcFile,%File)                 #! RA.2007.11.25
#ENDFOR                                 #! RA.2007.11.25
#!--------------------------------------------------------------!
#! List of table, key and rows names found in reserved list.    !
#!--------------------------------------------------------------!
#DECLARE(%ReservedFieldList),MULTI,UNIQUE
#EQUATE (%FoundFileName     ,'File Name:          ')
#EQUATE (%FoundFileField    ,'File Field Name:    ')
#EQUATE (%FoundAIKeyName    ,'AI Key Name:        ')
#EQUATE (%FoundKeyName      ,'Key Name:           ')
#EQUATE (%FoundKeyField     ,'Key Field Name:     ')
#EQUATE (%FoundRelatedName  ,'Related File Name:  ')
#EQUATE (%FoundRelatedField ,'Related Field Name: ')
#!-------------------------------------------------------------------------!
#! RA.2001.12.15 - Table of field-table to be able to create relations.    !
#!-------------------------------------------------------------------------!
#DECLARE(%FieldTableList),MULTI,UNIQUE
#!---------------------------------------------------------------------
#DECLARE(%LFieldInKey),MULTI,UNIQUE     #! Field list that are used in keys
#DECLARE(%IFieldInKey)                  #! Field item to search for in list
#DECLARE(%LFieldInKeyCount)             #! How many fields are in keys?
#FREE(%LFieldInKey)
#FOR(%ProcFile)                         #! RA.2007.11.25
  #FIX(%File,%ProcFile)                 #! RA.2007.11.25
  #IF(ITEMS(%Key))
    #FOR(%Key)
      #FOR(%KeyField)
        #ADD(%LFieldInKey,%KeyField)
      #ENDFOR
    #ENDFOR
  #ENDIF
#ENDFOR
#SET(%LFieldInKeyCount,ITEMS(%LFieldInKey))
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#INSERT(%ClarionReservedWords)
#INSERT(%SqlReservedWords)
#FOR(%ClarionWordList)
  #ADD(%SqlWordList,%ClarionWordList)
#ENDFOR
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#SET(%TableCount, 0)                    #! Count of tables
#SET(%AutoCount, 0)                     #! Count of Auto-Increment Keys
#SET(%PKeyCount, 0)                     #! Count of primary keys (From Keys)
#SET(%PKeyOption, 0)                    #! Count of primary keys (From Options)
#SET(%KeyOthers, 0)                     #! Count of secondary keys
#SET(%KeyCount, 0)                      #! Count of all keys combined
#SET(%FieldCount, 0)                    #! Count of fields
#SET(%RelCount, 0)                      #! Relationship Counts
#SET(%FieldUnique, 0)                   #! Count of unique fields
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#IF(%SQLTable)
  #SET(%OutTableName,%SQLDirectory & %SQLTableName)
  #CREATE(%OutTableName)
  #MESSAGE('The POSTGRESQL Script Creator',0)
  #MESSAGE('Creating: ' & %SQLTableName,1)
--
-- BOF: %OutTableName
--
-- SQL script creator for POSTGRESQL database [TABLE]
-- Created: %TheDate - %TheTime
--

#IF(%UseSchemaName)
CREATE SCHEMA %SchemaName
;

#ENDIF
#FOR(%ProcFile)                                                 #! RA.2007.11.25
  #FIX(%File,%ProcFile)                                         #! RA.2007.11.25
  #!---------------------------------------------------------------!
  #! RA.2001.12.01 - Ignore file place marker definitions.         !
  #! These are files that are defined but that contain no fields.  !
  #!---------------------------------------------------------------!
  #IF(NOT ITEMS(%Field))                                        #! RA.2001.12.01
    #CYCLE                                                      #! RA.2001.12.01
  #ENDIF                                                        #! RA.2001.12.01
  #SET(%nLoc1,INSTRING('RASQL(',%FileUserOptions,1,1))
  #SET(%nLoc2,INSTRING(')',%FileUserOptions,1,%nLoc1))
  #SET(%nFileUser,UPPER(SUB(%FileUserOptions,%nLoc1,%nLoc2)))
  #!SET(%NFileUser,UPPER(EXTRACT(%FileUserOptions,'RASQL')))
  #SET(%NFileOpt1,EXTRACT(%NFileUser,'RASQL',1))
  #SET(%NFileOpt2,EXTRACT(%NFileUser,'RASQL',2))
  #SET(%NFileOpt3,EXTRACT(%NFileUser,'RASQL',3))
  #SET(%NFileOpt4,EXTRACT(%NFileUser,'RASQL',4))
  #!-------------------------------------------------------------!
  #! The first option RASQL(NO) controls if a file is processed. !
  #!-------------------------------------------------------------!
  #IF(SUB(%NFileOpt1,1,1)='N')
    #CYCLE
  #ENDIF
#!
  #CASE(%FileType)
  #OF  ('VIEW')
  #OROF('ALIAS')
      #CYCLE
  #ENDCASE
#!
  #CASE(%FileDriver)
    #OF('ASCII')
  #OROF('BASIC')
  #OROF('DOS')
      #CYCLE
  #ENDCASE
#!
  #!-------------------------------------------------------------------------!
  #! RA.2007.09.20 - SQL file option to emulate auto increment (TRUE,FALSE). !
  #!-------------------------------------------------------------------------!
  #SET(%NFileOpt0,'')
  #SET(%nLoc1,INSTRING('EMULATEAUTONUMKEY(',UPPER(%FileUserOptions),1,1))
  #SET(%nLoc2,INSTRING(')',%FileUserOptions,1,%nLoc1))
  #SET(%nFileUser,UPPER(SUB(%FileUserOptions,%nLoc1,%nLoc2)))
  #SET(%NFileOpt0,EXTRACT(%NFileUser,'EMULATEAUTONUMKEY',1))
#!
  #MESSAGE('Table of: ' & %File,2)
  #SET(%NFieldPKey,UPPER(%FilePrefix) & '_PK')
#!
#INSERT(%PGModifyNameTable)
  #!------------------------------------------------------------------------!
  #! IF it IS a primary key, AND it is an integer, AND NOT auto-increment,  !
  #! AND there is one item in the key (only thing I can be sure about),     !
  #! THEN assume that it IS an auto-increment handled by the SQL backend.   !
  #!------------------------------------------------------------------------!
  #SET(%NFNameAuto,'')
  #SET(%AutoIncrementCount,0)
  #IF(ITEMS(%Key))
    #FOR(%Key)
#!---- Key        = %Key
      #IF(%KeyPrimary <> 1)
        #CYCLE
      #ENDIF
#!---- KeyPrimary = %KeyPrimary
      #IF(%KeyAuto = 1)
        #CYCLE
      #ENDIF
#!---- KeyAuto    = %KeyAuto
#!
      #IF(ITEMS(%KeyField) > 1)
        #CYCLE
      #ENDIF
#!
      #FOR(%KeyField)
#!---- KeyField   = %KeyField
        #FIX(%Field,%KeyField)
#!---- Field      = %Field
#!---- FieldType  = %FieldType
        #CASE(%FieldType)
        #OF('LONG')
          #SET(%NFNameAuto, SUB(%KeyField, INSTRING(':',%KeyField,1,1) + 1 , 50))
        #ENDCASE
      #ENDFOR
#!
      #IF(%NFNameAuto)
#!---- NFNameAuto = %NFNameAuto
        #SET(%AutoIncrementCount,%AutoIncrementCount + 1)
        #SET(%AutoCount, %AutoCount + 1)
#!        #SET(%NFNameAuto, SUB(%KeyAuto, INSTRING(':',%KeyAuto,1,1) + 1 , 50))
        #IF(%UsePrefix='All' OR %PrefixColumns)                                    #! RA.2008.08.11
          #SET(%NFNameAuto, %FilePrefix & '_' & %NFNameAuto)
        #ENDIF
        #!---------------------------------------------!
        #! RA.2007.10.10 - Behave according to option. !
        #!---------------------------------------------!
        #IF(NOT %IgnoreReserved)
          #SET(%SqlWordItem, UPPER(%NFNameAuto))
          #FIND(%SqlWordList, %SqlWordItem)
          #IF(%SqlWordList)
            #ADD(%ReservedFieldList, %FoundAIKeyName & %FilePrefix & ':' & %NFNameAuto)
            #SET(%NFNameAuto, %NFNameAuto & '_')
          #ENDIF
        #ENDIF
      #ENDIF
    #ENDFOR
    #IF(%AutoIncrementCount = 0)
#!-- WARNING: %File(%FilePrefix) has no AUTO-INCREMENT KEY defined.
    #ENDIF
  #ENDIF
#!
#!PRINT '%NFileName'
#!
  #IF(%nFileOpt0)
--
-- DRIVER:  %FileDriver
-- OPTIONS: %FileDriverParameter
-- OWNER:   %FileOwner
-- NAME:    %FileName
--
  #ENDIF
#!
CREATE TABLE %NFileName (
#INSERT(%PGSQLField)                                            #! Process fields
)
;
COMMENT ON TABLE %NFileName IS
'%FileDescription'
;
#INSERT(%PGSQLFieldComment)                                     #! Process fields comments

#!  #IF (%UseOwner)
#!ALTER TABLE %NFileName OWNER TO %OwnerName
#!;
#!
#!  #ENDIF
  #!----------------------------------------------------------------!
  #! RA.2007.09.20 - If the ISIDENTITY() is set to TRUE this is     !
  #! an auto increment field. This is best descrived as a SERIAL    !
  #! (or BIG serial). SERIAL or BIGSERIAL sets up the equivalent    !
  #! in PostgreSQL of:                                              !
  #!                                                                !
  #!  CREATE TABLE pg_name (                                        !
  #!    auto_id INT NOT NULL ,                                      !
  #!    first_name CHAR(30) NOT NULL ,                              !
  #!    middle_name CHAR(30) NULL ,                                 !
  #!    last_name CHAR(30) NOT NULL ,                               !
  #!    last_name_father CHAR(30) NULL ,                            !
  #!    last_name_mother CHAR(30) NULL                              !
  #!  )                                                             !
  #!  ;                                                             !
  #!                                                                !
  #!  ALTER TABLE pg_name                                           !
  #!    ADD CONSTRAINT pg01_key0_pk PRIMARY KEY (auto_id)           !
  #!  ;                                                             !
  #!                                                                !
  #!  CREATE INDEX pg01_key1                                        !
  #!    ON pg_name (last_name)                                      !
  #!  ;                                                             !
  #!                                                                !
  #!  CREATE SEQUENCE pg_name_id_seq                                !
  #!      INCREMENT BY 1                                            !
  #!      CACHE 1                                                   !
  #!  ;                                                             !
  #!                                                                !
  #!  ALTER SEQUENCE pg_name_id_seq OWNED BY pg_name.auto_id        !
  #!  ;                                                             !
  #!                                                                !
  #!  ALTER TABLE pg_name ALTER COLUMN auto_id SET DEFAULT          !
  #!       nextval('pg_name_id_seq'::regclass)                      !
  #!  ;                                                             !
  #!                                                                !
  #!----------------------------------------------------------------!
  #IF(%nFileOpt0)                                               #! You have to have it set up in the dictionary
    #IF(%UseSerial)                                             #! And you have to want it in the template
CREATE SEQUENCE %NFileName_id_seq
  INCREMENT BY 1
  CACHE 1
;
COMMENT ON SEQUENCE %NFileName_id_seq IS
'%FileDescription'
;

ALTER SEQUENCE %NFileName_id_seq
  OWNED BY %NFileName.%NFNameAuto
;

ALTER TABLE %NFileName ALTER COLUMN %NFNameAuto
  SET DEFAULT nextval('%NFileName_id_seq'::regclass)
;
    #ENDIF

  #ENDIF
#ENDFOR
--
-- EOF: %OutTableName
--
  #CLOSE(%OutTableName)
#ENDIF
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#IF(%SQLGrant)
  #SET(%OutGrantName,%SQLDirectory & %SQLGrantName)
  #CREATE(%OutGrantName)
  #MESSAGE('The POSTGRESQL Script Creator',0)
  #MESSAGE('Creating: ' & %SQLGrantName,1)
--
-- BOF: %OutGrantName
--
-- SQL script creator for POSTGRESQL database [GRANT]
-- Created: %TheDate - %TheTime
--

#IF(%UseSchemaName)
SET search_path TO %SchemaName, pg_catalog
;

#ENDIF
#IF(%optGrantAll)
GRANT ALL TO %optGrantAllUser WITH GRANT OPTION
;

#ENDIF
#FOR(%ProcFile)                                                 #! RA.2007.11.25
  #FIX(%File,%ProcFile)                                         #! RA.2007.11.25
  #!---------------------------------------------------------------!
  #! RA.2001.12.01 - Ignore file place marker definitions.         !
  #! These are files that are defined but that contain no fields.  !
  #!---------------------------------------------------------------!
  #IF(NOT ITEMS(%Field))                                        #! RA.2001.12.01
    #CYCLE                                                      #! RA.2001.12.01
  #ENDIF                                                        #! RA.2001.12.01
  #SET(%nLoc1,INSTRING('RASQL(',%FileUserOptions,1,1))
  #SET(%nLoc2,INSTRING(')',%FileUserOptions,1,%nLoc1))
  #SET(%nFileUser,UPPER(SUB(%FileUserOptions,%nLoc1,%nLoc2)))
  #!SET(%NFileUser,UPPER(EXTRACT(%FileUserOptions,'RASQL')))
  #SET(%NFileOpt1,EXTRACT(%NFileUser,'RASQL',1))
  #SET(%NFileOpt2,EXTRACT(%NFileUser,'RASQL',2))
  #SET(%NFileOpt3,EXTRACT(%NFileUser,'RASQL',3))
  #SET(%NFileOpt4,EXTRACT(%NFileUser,'RASQL',4))
  #!-------------------------------------------------------------!
  #! The first option RASQL(NO) controls if a file is processed. !
  #!-------------------------------------------------------------!
  #IF(SUB(%NFileOpt1,1,1)='N')
    #CYCLE
  #ENDIF
#!
  #CASE(%FileType)
  #OF  ('VIEW')
  #OROF('ALIAS')
      #CYCLE
  #ENDCASE
#!
  #CASE(%FileDriver)
    #OF('ASCII')
  #OROF('BASIC')
  #OROF('DOS')
      #CYCLE
  #ENDCASE
#!
  #!-------------------------------------------------------------------------!
  #! RA.2007.09.20 - SQL file option to emulate auto increment (TRUE,FALSE). !
  #!-------------------------------------------------------------------------!
  #SET(%NFileOpt0,'')
  #SET(%nLoc1,INSTRING('EMULATEAUTONUMKEY(',UPPER(%FileUserOptions),1,1))
  #SET(%nLoc2,INSTRING(')',%FileUserOptions,1,%nLoc1))
  #SET(%nFileUser,UPPER(SUB(%FileUserOptions,%nLoc1,%nLoc2)))
  #SET(%NFileOpt0,EXTRACT(%NFileUser,'EMULATEAUTONUMKEY',1))
#!
  #MESSAGE('Table of: ' & %File,2)
  #SET(%NFieldPKey,UPPER(%FilePrefix) & '_PK')
#!
#INSERT(%PGModifyNameTable)
#!
--
-- TABLE: %NFileName
--
  #FOR(%optGrant)
    #SET(%optGrantTotal     , %optGrantSelect + %optGrantInsert + %optGrantUpdate + %optGrantDelete)
    #SET(%optGrantCount     , 0)
    #SET(%optGrantLine      , '')
#!-- TOTAL: %optGrantTotal
    #CASE(%optGrantTotal)
    #OF(4)
      #SET(%optGrantLine, 'GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE ' & %NFileName)
      #SET(%optGrantCount, 4)
    #OF(3)
      #SET(%optGrantLine, 'GRANT')
      #IF(%optGrantSelect)
        #SET(%optGrantLine, %optGrantLine & ' SELECT,')
        #SET(%optGrantCount, %optGrantCount + 1)
      #ENDIF
#!-- COUNT(1): %optGrantCount
      #IF(%optGrantInsert)
        #SET(%optGrantLine, %optGrantLine & ' INSERT,')
        #SET(%optGrantCount, %optGrantCount + 1)
      #ENDIF
#!-- COUNT(2): %optGrantCount
      #IF(%optGrantUpdate)
        #SET(%optGrantLine, %optGrantLine & ' UPDATE')
        #SET(%optGrantCount, %optGrantCount + 1)
        #IF(%optGrantCount = %optGrantTotal)
          #SET(%optGrantLine, %optGrantLine & ' ON TABLE ' & %NFileName)
        #ELSE
          #SET(%optGrantLine, %optGrantLine & ',')
        #ENDIF
      #ENDIF
#!-- COUNT(3): %optGrantCount
      #IF(%optGrantDelete)
        #SET(%optGrantLine, %optGrantLine & ' DELETE')
        #SET(%optGrantCount, %optGrantCount + 1)
        #IF(%optGrantCount = %optGrantTotal)
          #SET(%optGrantLine, %optGrantLine & ' ON TABLE ' & %NFileName)
        #ELSE
          #SET(%optGrantLine, %optGrantLine & ',')
        #ENDIF
      #ENDIF
#!-- COUNT(4): %optGrantCount
    #OF(2)
      #SET(%optGrantLine, 'GRANT')
      #IF(%optGrantSelect)
        #SET(%optGrantLine, %optGrantLine & ' SELECT,')
        #SET(%optGrantCount, %optGrantCount + 1)
      #ENDIF
#!-- COUNT(1): %optGrantCount
      #IF(%optGrantInsert)
        #SET(%optGrantLine, %optGrantLine & ' INSERT')
        #SET(%optGrantCount, %optGrantCount + 1)
        #IF(%optGrantCount = %optGrantTotal)
          #SET(%optGrantLine, %optGrantLine & ' ON TABLE ' & %NFileName)
        #ELSE
          #SET(%optGrantLine, %optGrantLine & ',')
        #ENDIF
      #ENDIF
#!-- COUNT(2): %optGrantCount
      #IF(%optGrantUpdate)
        #SET(%optGrantLine, %optGrantLine & ' UPDATE')
        #SET(%optGrantCount, %optGrantCount + 1)
        #IF(%optGrantCount = %optGrantTotal)
          #SET(%optGrantLine, %optGrantLine & ' ON TABLE ' & %NFileName)
        #ELSE
          #SET(%optGrantLine, %optGrantLine & ',')
        #ENDIF
      #ENDIF
#!-- COUNT(3): %optGrantCount
      #IF(%optGrantDelete)
        #SET(%optGrantLine, %optGrantLine & ' DELETE')
        #SET(%optGrantCount, %optGrantCount + 1)
        #IF(%optGrantCount = %optGrantTotal)
          #SET(%optGrantLine, %optGrantLine & ' ON TABLE ' & %NFileName)
        #ELSE
          #SET(%optGrantLine, %optGrantLine & ',')
        #ENDIF
      #ENDIF
#!-- COUNT(4): %optGrantCount
    #OF(1)
      #SET(%optGrantLine, 'GRANT')
      #IF(%optGrantSelect)
        #SET(%optGrantLine, %optGrantLine & ' SELECT')
        #SET(%optGrantCount, %optGrantCount + 1)
        #IF(%optGrantCount = %optGrantTotal)
          #SET(%optGrantLine, %optGrantLine & ' ON TABLE ' & %NFileName)
        #ELSE
          #SET(%optGrantLine, %optGrantLine & ',')
        #ENDIF
      #ENDIF
#!-- COUNT(1): %optGrantCount
      #IF(%optGrantInsert)
        #SET(%optGrantLine, %optGrantLine & ' INSERT')
        #SET(%optGrantCount, %optGrantCount + 1)
        #IF(%optGrantCount = %optGrantTotal)
          #SET(%optGrantLine, %optGrantLine & ' ON TABLE ' & %NFileName)
        #ELSE
          #SET(%optGrantLine, %optGrantLine & ',')
        #ENDIF
      #ENDIF
#!-- COUNT(2): %optGrantCount
      #IF(%optGrantUpdate)
        #SET(%optGrantLine, %optGrantLine & ' UPDATE')
        #SET(%optGrantCount, %optGrantCount + 1)
        #IF(%optGrantCount = %optGrantTotal)
          #SET(%optGrantLine, %optGrantLine & ' ON TABLE ' & %NFileName)
        #ELSE
          #SET(%optGrantLine, %optGrantLine & ',')
        #ENDIF
      #ENDIF
#!-- COUNT(3): %optGrantCount
      #IF(%optGrantDelete)
        #SET(%optGrantLine, %optGrantLine & ' DELETE')
        #SET(%optGrantCount, %optGrantCount + 1)
        #IF(%optGrantCount = %optGrantTotal)
          #SET(%optGrantLine, %optGrantLine & ' ON TABLE ' & %NFileName)
        #ELSE
          #SET(%optGrantLine, %optGrantLine & ',')
        #ENDIF
      #ENDIF
#!-- COUNT(4): %optGrantCount
    #ENDCASE
%optGrantLine
    #IF(%optGrantType = 'User')
  TO %optGrantName
    #ELSE
  TO GROUP %optGrantName
    #ENDIF
;
  #ENDFOR

#ENDFOR
--
-- EOF: %OutGrantName
--
  #CLOSE(%OutGrantName)
#ENDIF
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#IF(%SQLIndex)
  #SET(%OutIndexName,%SQLDirectory & %SQLIndexName)
  #CREATE(%OutIndexName)
  #MESSAGE('The POSTGRESQL Script Creator',0)
  #MESSAGE('Creating: ' & %SQLIndexName,1)
--
-- BOF: %OutIndexName
--
-- SQL script creator for POSTGRESQL database [KEYS]
-- Created: %TheDate - %TheTime
--

#IF(%UseSchemaName)
SET search_path TO %SchemaName, pg_catalog
;

#ENDIF
#FOR(%ProcFile)                                                 #! RA.2007.11.25
  #FIX(%File,%ProcFile)                                         #! RA.2007.11.25
  #!---------------------------------------------------------------!
  #! RA.2001.12.01 - Ignore file place marker definitions.         !
  #! These are files that are defined but that contain no fields.  !
  #!---------------------------------------------------------------!
  #IF(NOT ITEMS(%Field))                                        #! RA.2001.12.01
    #CYCLE                                                      #! RA.2001.12.01
  #ENDIF                                                        #! RA.2001.12.01
  #SET(%nLoc1,INSTRING('RASQL(',%FileUserOptions,1,1))
  #SET(%nLoc2,INSTRING(')',%FileUserOptions,1,%nLoc1))
  #SET(%nFileUser,UPPER(SUB(%FileUserOptions,%nLoc1,%nLoc2)))
  #!SET(%NFileUser,UPPER(EXTRACT(%FileUserOptions,'RASQL')))
  #SET(%NFileOpt1,EXTRACT(%NFileUser,'RASQL',1))
  #SET(%NFileOpt2,EXTRACT(%NFileUser,'RASQL',2))
  #SET(%NFileOpt3,EXTRACT(%NFileUser,'RASQL',3))
  #SET(%NFileOpt4,EXTRACT(%NFileUser,'RASQL',4))
  #!-------------------------------------------------------------!
  #! The first option RASQL(NO) controls if a file is processed. !
  #!-------------------------------------------------------------!
  #IF(SUB(%NFileOpt1,1,1)='N')
    #CYCLE
  #ENDIF
#!
  #CASE(%FileType)
  #OF  ('VIEW')
  #OROF('ALIAS')
      #CYCLE
  #ENDCASE
#!
  #CASE(%FileDriver)
    #OF('ASCII')
  #OROF('BASIC')
  #OROF('DOS')
      #CYCLE
  #ENDCASE
#!
  #!-------------------------------------------------------------------------!
  #! RA.2007.09.20 - SQL file option to emulate auto increment (TRUE,FALSE). !
  #!-------------------------------------------------------------------------!
  #SET(%NFileOpt0,'')
  #SET(%nLoc1,INSTRING('EMULATEAUTONUMKEY(',UPPER(%FileUserOptions),1,1))
  #SET(%nLoc2,INSTRING(')',%FileUserOptions,1,%nLoc1))
  #SET(%nFileUser,UPPER(SUB(%FileUserOptions,%nLoc1,%nLoc2)))
  #SET(%NFileOpt0,EXTRACT(%NFileUser,'EMULATEAUTONUMKEY',1))
#!
  #MESSAGE('Table of: ' & %File,2)
  #SET(%NFieldPKey,UPPER(%FilePrefix) & '_PK')
#!
#INSERT(%PGModifyNameTable)
#!
#!PRINT '%NFileName'
  #SET(%PrimaryCount,0)                                         #! Primary key exists
  #SET(%SecondaryCount,0)                                       #! Secondary key exists
  #IF(ITEMS(%Key))
    #FOR(%Key)
      #IF(NOT ITEMS(%KeyField))                                 #! Key has fields? No!
        #CYCLE                                                  #! Then go to the next
      #ENDIF                                                    #! key to be processed
#!
      #SET(%nLoc1,INSTRING('RASQL(',%KeyUserOptions,1,1))
      #SET(%nLoc2,INSTRING(')',%KeyUserOptions,1,%nLoc1))
      #SET(%nKeyUser,UPPER(SUB(%KeyUserOptions,%nLoc1,%nLoc2)))
      #!SET(%NKeyUser,UPPER(EXTRACT(%KeyUserOptions,'RASQL')))
      #SET(%NKeyOpt1,EXTRACT(%NKeyUser,'RASQL',1))
      #SET(%NKeyOpt2,EXTRACT(%NKeyUser,'RASQL',2))
      #SET(%NKeyOpt3,EXTRACT(%NKeyUser,'RASQL',3))
      #SET(%NKeyOpt4,EXTRACT(%NKeyUser,'RASQL',4))
#!%NKeyUser, %NKeyOpt1, %NKeyOpt2, %NKeyOpt3, %NKeyOpt4
      #!------------------------------------------------------------!
      #! The first option RASQL(NO) controls if a key is processed. !
      #!------------------------------------------------------------!
      #IF(SUB(%NKeyOpt1,1,1)='N')
        #CYCLE
      #ENDIF
#!
#! -- FileName 00: %NFileName
#INSERT(%PGModifyNameKey00)
#! -- FileName 01: %NFileName
#!
      #SET(%NKeyFields,'')
      #SET(%NKeyFieldCount,0)
      #FOR(%KeyField)
        #SET(%NKeyField, SUB(%KeyField, INSTRING(':',%KeyField,1,1) + 1 , 50))
        #!-------------------------------------------------------------------------!
        #! RA.2001.12.15 - Table of keyfield-table to be able to create relations. !
        #!-------------------------------------------------------------------------!
        #IF(%DoFieldFile)
          #ADD(%FieldTableList, %NKeyField & ' ==> ' & %File)
        #ENDIF
        #!
        #IF(%UsePrefix='All' OR %PrefixColumns)                    #! RA.2008.08.11
          #SET(%NKeyField, %FilePrefix & '_' & %NKeyField)
        #ENDIF
        #!----------------------------------!
        #! RA.2007.12.26 - Obey the option. !
        #!----------------------------------!
        #IF(NOT %IgnoreReserved)
          #SET(%SqlWordItem, UPPER(%NKeyField))
#!-- SqlWordItem = '%SqlWordItem'
          #FIND(%SqlWordList, %SqlWordItem)
#!-- SqlWordList = '%SqlWordList'
          #IF(%SqlWordList)
            #ADD(%ReservedFieldList, %FoundKeyField & %FilePrefix & ':' & %NKeyField)
            #SET(%NKeyField, %NKeyField & '_')
          #ENDIF
        #ENDIF
        #SET(%NKeyFieldCount,%NKeyFieldCount + 1 )
        #IF(%NKeyFieldCount = 1)
          #SET(%NKeyFields,%NKeyField)
        #ELSE
          #SET(%NKeyFields, %NKeyFields & ', ' & %NKeyField)
        #ENDIF
      #ENDFOR
      #IF(%KeyPrimary)
        #SET(%PrimaryCount, 1)
        #SET(%PKeyCount, %PKeyCount + 1)
        #SET(%KeyCount, %KeyCount + 1)
ALTER TABLE %NFileName
  ADD CONSTRAINT %NKeyName PRIMARY KEY (%NKeyFields)
;
COMMENT ON CONSTRAINT %NKeyName ON %NFileName IS
'%KeyDescription'
;

      #ELSIF(NOT %KeyPrimary)
        #SET(%KeyOthers, %KeyOthers + 1)
        #SET(%KeyCount, %KeyCount + 1)
        #IF(%KeyDuplicate)
          #CASE(%KeyIndex)
          #OF  ('KEY')
CREATE INDEX %NKeyName
 #IF(%UseLowerCase)                                    #! MAG 02.21.08
  #SET(%NKeyFields,lower(%NKeyFields))                 #! MAG 02.21.08
 #ENDIF                                                #! MAG 02.21.08
  ON %NFileName (%NKeyFields)
;
#IF(%UseLowerCase)                                     #! MAG 02.21.08
 #SET(%NKeyName,lower(%NKeyName))                      #! MAG 02.21.08
#ENDIF                                                 #! MAG 02.21.08

COMMENT ON INDEX %NKeyName IS
'%KeyDescription'
;
           #OF  ('INDEX')
          #OROF('DYNAMIC')
#IF(%UseLowerCase)                                     #! MAG 02.21.08
 #SET(%NKeyName, lower(%NKeyName))                     #! MAG 02.21.08
 #SET(%NKeyFields, lower(%NKeyFields))                 #! MAG 02.21.08
#ENDIF                                                 #! MAG 02.21.08
CREATE INDEX %NKeyName
  ON %NFileName (%NKeyFields)
;
COMMENT ON INDEX %NKeyName IS
'%KeyDescription'
;

          #ENDCASE
        #ELSE
          #CASE(%KeyIndex)
          #OF  ('KEY')

CREATE UNIQUE INDEX %NKeyName
#IF(%UseLowerCase)                                      #! MAG 02.21.08
  #SET(%NKeyFields,Lower(%NKeyFields))                  #! RA.2008.03.22 - Correction
#ENDIF                                                  #! MAG 02.21.08
  ON %NFileName (%NKeyFields)
;
COMMENT ON INDEX %NKeyName IS
'%KeyDescription'
;

          #OF  ('INDEX')
          #OROF('DYNAMIC')
CREATE UNIQUE INDEX %NKeyName
  #IF(%UseLowerCase)                                    #! MAG 02.21.08
  ON %NFileName (lower(%NKeyFields))
  #ELSE                                                 #! MAG 02.21.08
  ON %NFileName (%NKeyFields)
  #ENDIF                                                #! MAG 02.21.08
;
COMMENT ON INDEX %NKeyName IS
'%KeyDescription'
;

          #ENDCASE
        #ENDIF
      #ENDIF
    #ENDFOR
    #IF(NOT %PrimaryCount)
--
-- WARNING: %File(%FilePrefix) has no PRIMARY KEY defined.
--
    #ENDIF
  #ELSE
--
-- WARNING: %File(%FilePrefix) has no KEYS defined.
--
  #ENDIF
#ENDFOR
--
-- EOF: %OutIndexName
--
  #CLOSE(%OutIndexName)
#ENDIF
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#IF(%SQLRelat)
  #SET(%OutRelatName,%SQLDirectory & %SQLRelatName)
  #CREATE(%OutRelatName)
  #MESSAGE('The POSTGRESQL Script Creator',0)
  #MESSAGE('Creating: ' & %SQLRelatName,1)
--
-- BOF: %OutRelatName
--
-- SQL script creator for POSTGRESQL database [RELATIONS]
-- Created: %TheDate - %TheTime
--

#IF(%UseSchemaName)
SET search_path TO %SchemaName, pg_catalog
;

#ENDIF
#!
  #SET(%RelCount, 0)
  #FOR(%ProcFile)                           #! RA.2007.11.25
    #FIX(%File,%ProcFile)                   #! RA.2007.11.25
    #CASE(%FileDriver)
      #OF('ASCII')
    #OROF('BASIC')
    #OROF('DOS')
        #CYCLE
    #ENDCASE
#!
    #CASE(%FileType)
    #OF  ('VIEW')
#!------------------------------------------------------------!
#! RA.2001.07.22 - Need to deal with aliases for the purpose  !
#! of discovering additional key relationships that can not   !
#! be done with a single file from Clarion. In specific       !
#!                                                            !
#! Tables                                                     !
#!  Party_Table         Party_Relationship_Table              !
#!  Party_Id            Party_Id_From                         !
#!                      Party_Id_To                           !
#! Relations                                                  !
#!  Party_Id    <->>    Party_Id_From                         !
#!  Party_Id    <->>    Party_Id_To                           !
#!                                                            !
#! Clarion would need an alias of Party_Relationship_Table to !
#! deal with the second relationship. There would be no way   !
#! to use the second table again since Clarion restricts that !
#! specific operation.                                        !
#!                                                            !
#! SQL handles these type of relationships correctly and they !
#! need to be generated as an original file relationship.     !
#!------------------------------------------------------------!
#!  #OROF('ALIAS')
        #CYCLE
    #ENDCASE
#!
#INSERT(%PGModifyNameTable)
#!
    #SET(%TheCount, 0)
    #IF(ITEMS(%Relation))
      #FOR(%Relation)
        #SET(%nLoc1,INSTRING('RASQL(',%RelationUserOptions,1,1))
        #SET(%nLoc2,INSTRING(')',%RelationUserOptions,1,%nLoc1))
        #SET(%nRelationUser,UPPER(SUB(%RelationUserOptions,%nLoc1,%nLoc2)))
        #!SET(%NRelationUser,UPPER(EXTRACT(%RelationUserOptions,'RASQL')))
        #SET(%NRelationOpt1,EXTRACT(%NRelationUser,'RASQL',1))
        #SET(%NRelationOpt2,EXTRACT(%NRelationUser,'RASQL',2))
        #SET(%NRelationOpt3,EXTRACT(%NRelationUser,'RASQL',3))
        #SET(%NRelationOpt4,EXTRACT(%NRelationUser,'RASQL',4))
#!-- %FileRelationType
#!-- %NFileName  <=  %NRelateFile [ %FileType ] %AliasFile : %Relation  ==  %RelationAlias
#!-- %NRelationUser, %NRelationOpt1, %NRelationOpt2, %NRelationOpt3, %NRelationOpt4
        #!-----------------------------------------------------------------!
        #! The first option RASQL(NO) controls if a relation is processed. !
        #!-----------------------------------------------------------------!
        #IF(SUB(%NRelationOpt1,1,1)='N')
          #CYCLE
        #ENDIF
#!
        #!--------------------------------------------------------------------!
        #! RA.2007.10.02 - References always go from the child to the parent. !
        #!--------------------------------------------------------------------!
        #IF(%FileRelationType = 'MANY:1')
          #SET(%Relationfield1, '')
          #SET(%Relationfield2, '')
#INSERT(%PGModifyNameKey01)
          #!
#INSERT(%PGModifyNameRelTable)
#!-- [%FileRelationType] %NFileName  <=  %NRelateFile [ %FileType ] %AliasFile : %Relation  ==  %RelationAlias
          #!
          #SET(%RelationField1Count, ITEMS(%FileKeyField))
          #SET(%RelationField2Count, ITEMS(%RelationKeyField))
          #!
          #SET(%NRelateCount, %RelationField1Count)
          #SET(%NRelateComma, %NRelateCount - 1)
          #SET(%NRelateItem, 0)
          #FOR(%FileKeyField)
            #IF(%NRelateItem > %NRelateCount)
              #BREAK
            #ENDIF
            #SET(%NRelateItem, %NRelateItem + 1)
            #SET(%NRelateField1, SUB(%FileKeyField,INSTRING(':',%FileKeyField,1,1)+1,50))
            #SET(%NRelateField2, SUB(%FileKeyFieldLink,INSTRING(':',%FileKeyFieldLink,1,1)+1,50))
            #!
#INSERT(%PGModifyNameRelField1)
            #!
#INSERT(%PGModifyNameRelField2)
            #!
            #SET(%RelationField1, %RelationField1 & %NRelateField1)
            #SET(%RelationField2, %RelationField2 & %NRelateField2)
            #IF(%NRelateComma)
              #SET(%NRelateComma, %NRelateComma - 1)
              #SET(%RelationField1, %RelationField1 & ', ')
              #SET(%RelationField2, %RelationField2 & ', ')
            #ENDIF
          #ENDFOR
          #!-------------------------------------------!
          #! RA.2001.01.20 - Added this feature.       !
          #! Has syntax errors, so commented out.      !
          #!-------------------------------------------!
          #! Added to the comments so programmer is at !
          #! aware that other relationships may exist. !
          #!-------------------------------------------!
          #SET(%RelationUpdate,%RelationConstraintUpdate)       #! ON UPDATE ?
          #SET(%RelationDelete,%RelationConstraintDelete)       #! ON DELETE ?
          #!-------------------------------------------!
          #IF(%Relationfield1)
            #MESSAGE('Relation of: ' & %File,2)
            #SET(%RelCount, %RelCount + 1)
            #!----------------------------------------------------------!
            #! RA.2001.07.22 - The alias name gets changed to the real  !
            #! name to generate the SQL statement for the relationship. !
            #!----------------------------------------------------------!
            #IF(%FileType = 'ALIAS')
              #SET(%NFileName,%AliasFile)
              #FIX(%File, %AliasFile)
              #IF(%UsePrefix='All' OR %PrefixTables)                    #! RA.2008.08.11
                #SET(%NFileName,%FilePrefix & '_' & %File)
              #ENDIF
            #ENDIF
            #IF(%TheCount = 0)
#!PRINT '%NFileName'
            #ENDIF
            #SET(%TheCount,%TheCount+1)
ALTER TABLE %NFileName
   ADD CONSTRAINT rel_%NKeyField
   FOREIGN KEY (%Relationfield1)
   REFERENCES %NRelateFile (%Relationfield2)
   #!--------------------------------------------------------!
   #! RA.2007.10.03 - Work on this - PostgreSQL supports it. !
   #! MG.2008.07.17 - Completed the work on the Relationships!
   #!--------------------------------------------------------!
            #IF(%RelationUpdate)
            #CASE(%RelationUpdate)
            #OF('RESTRICT_SERVER')
   ON UPDATE RESTRICT
            #OF('CASCADE_SERVER')
   ON UPDATE CASCADE
            #OF('CLEAR_SERVER')
            #OROF('CLEAR')
   ON UPDATE SET NULL
            #OF('NONE')
            #OROF('NONE_SERVER')
            #ELSE
   ON UPDATE %RelationUpdate
            #ENDCASE
            #ENDIF
            #IF(%RelationDelete)
            #CASE(%RelationDelete)
            #OF('RESTRICT_SERVER')
   ON DELETE RESTRICT
            #OF('CASCADE_SERVER')
   ON DELETE CASCADE
            #OF('CLEAR_SERVER')
            #OROF('CLEAR')
   ON DELETE SET NULL
            #OF('NONE')
            #OROF('NONE_SERVER')
            #ELSE
   ON DELETE %RelationDelete
            #ENDCASE
            #ENDIF
            #IF(%SQLRelatDefer)
            #IF(SUB(%NRelationOpt3,1,1) = 'Y')
   DEFERRABLE
   INITIALLY DEFERRED
           #ENDIF
           #ENDIF
;

          #ENDIF
        #ENDIF
      #ENDFOR
    #ENDIF
  #ENDFOR

--
-- EOF: %OutRelatName
--
  #CLOSE(%OutRelatName)
#ENDIF
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#IF(%SQLValid)
  #SET(%OutValidName,%SQLDirectory & %SQLValidName)
  #CREATE(%OutValidName)
  #MESSAGE('The POSTGRESQL Script Creator',0)
  #MESSAGE('Creating: ' & %SQLValidName,1)
--
-- BOF: %OutValidName
--
-- SQL script creator for POSTGRESQL database [VALIDATIONS]
-- Created: %TheDate - %TheTime
--

#IF(%UseSchemaName)
SET search_path TO %SchemaName, pg_catalog
;

#ENDIF
#FOR(%ProcFile)                                                 #! RA.2007.11.25
  #FIX(%File,%ProcFile)                                         #! RA.2007.11.25
  #!---------------------------------------------------------------!
  #! RA.2001.12.01 - Ignore file place marker definitions.         !
  #! These are files that are defined but that contain no fields.  !
  #!---------------------------------------------------------------!
  #IF(NOT ITEMS(%Field))                                        #! RA.2001.12.01
    #CYCLE                                                      #! RA.2001.12.01
  #ENDIF                                                        #! RA.2001.12.01
  #SET(%nLoc1,INSTRING('RASQL(',%FileUserOptions,1,1))
  #SET(%nLoc2,INSTRING(')',%FileUserOptions,1,%nLoc1))
  #SET(%nFileUser,UPPER(SUB(%FileUserOptions,%nLoc1,%nLoc2)))
  #!SET(%NFileUser,UPPER(EXTRACT(%FileUserOptions,'RASQL')))
  #SET(%NFileOpt1,EXTRACT(%NFileUser,'RASQL',1))
  #SET(%NFileOpt2,EXTRACT(%NFileUser,'RASQL',2))
  #SET(%NFileOpt3,EXTRACT(%NFileUser,'RASQL',3))
  #SET(%NFileOpt4,EXTRACT(%NFileUser,'RASQL',4))
  #!-------------------------------------------------------------!
  #! The first option RASQL(NO) controls if a file is processed. !
  #!-------------------------------------------------------------!
  #IF(SUB(%NFileOpt1,1,1)='N')
    #CYCLE
  #ENDIF
#!
  #CASE(%FileType)
  #OF  ('VIEW')
  #OROF('ALIAS')
      #CYCLE
  #ENDCASE
#!
  #CASE(%FileDriver)
    #OF('ASCII')
  #OROF('BASIC')
  #OROF('DOS')
  #OROF('IN-MEMORY')
      #CYCLE
  #ENDCASE
#!
  #!-------------------------------------------------------------------------!
  #! RA.2007.09.20 - SQL file option to emulate auto increment (TRUE,FALSE). !
  #!-------------------------------------------------------------------------!
  #SET(%NFileOpt0,'')
  #SET(%nLoc1,INSTRING('EMULATEAUTONUMKEY(',UPPER(%FileUserOptions),1,1))
  #SET(%nLoc2,INSTRING(')',%FileUserOptions,1,%nLoc1))
  #SET(%nFileUser,UPPER(SUB(%FileUserOptions,%nLoc1,%nLoc2)))
  #SET(%NFileOpt0,EXTRACT(%NFileUser,'EMULATEAUTONUMKEY',1))
#!
  #MESSAGE('Table of: ' & %File,2)
  #SET(%NFieldPKey,UPPER(%FilePrefix) & '_PK')
#!
#INSERT(%PGModifyNameTable)
#!
  #FOR(%Field)
    #SET(%nLoc1,INSTRING('RASQL(',%FieldUserOptions,1,1))
    #SET(%nLoc2,INSTRING(')',%FieldUserOptions,1,%nLoc1))
    #SET(%nFieldUser,UPPER(SUB(%FieldUserOptions,%nLoc1,%nLoc2)))
    #!SET(%NFieldUser,UPPER(EXTRACT(%FieldUserOptions,'RASQL')))
    #SET(%NFieldOpt1,EXTRACT(%NFieldUser,'RASQL',1))
    #SET(%NFieldOpt2,EXTRACT(%NFieldUser,'RASQL',2))
    #SET(%NFieldOpt3,EXTRACT(%NFieldUser,'RASQL',3))
    #SET(%NFieldOpt4,EXTRACT(%NFieldUser,'RASQL',4))
    #!%NFieldUser, %NFieldOpt1, %NFieldOpt2, %NFieldOpt3, %NFieldOpt4
    #!--------------------------------------------------------------!
    #! The first option RASQL(NO) controls if a field is processed. !
    #!--------------------------------------------------------------!
    #IF(SUB(%NFieldOpt1,1,1)='N')
      #CYCLE
    #ENDIF
    #!------------------------------------------------!
    #! Begin removal of types that are not processed. !
    #!------------------------------------------------!
    #IF(%FieldType='GROUP')
      #CYCLE
    #ENDIF
    #IF(%FieldType='END')
      #CYCLE
    #ENDIF
    #!--------------------------------------------!
    #! RA.2007.10.03 - Simple name for functions. !
    #!--------------------------------------------!
    #SET(%NFieldName, %FieldID)                                     #! Field Name
    #!
    #SET(%ValTypeDCT        ,UPPER(%FieldType))                     #! Original type from dictionary
    #SET(%ValType           ,'')                                    #! Validation type for backend
    #SET(%ValLength         ,%FieldMemoSize)                        #! Length in characters
    #SET(%ValNumeric        ,%FieldFormatWidth)                     #! Numer of characters in entry control
    #SET(%ValDecimal        ,%FieldPlaces)                          #! Number of decimal places
    #SET(%ValTrue           ,%FieldTrueValue)                       #! TRUE Value
    #SET(%ValFalse          ,%FieldFalseValue)                      #! FALSE Value
    #FREE(%ValList)                                                 #! INLIST values
    #!------------------------------------------------------!
    #! RA.2007.10.04 - Figure type of variable for backend. !
    #!------------------------------------------------------!
    #CASE(%ValTypeDCT)
    #OF('BLOB')                                                     #! This should not make it thru
      #SET(%ValType,'bytea')                                        #! since it is binary data
    #OF('MEMO')
      #SET(%ValType,'text')
    #OF('CSTRING')
      #SET(%ValLength,%ValLength - 1)                               #! Adjust for null terminator
    #ENDCASE
    #!=============================================================!
    #! In some cases it is more accurate to check the display mask !
    #! to determine the web type. Dates can be a LONG or a DATE    !
    #! dictionary type, and the first part of the display mask is  !
    #! going to always be a '@D.                                   !
    #!=============================================================!
    #CASE(SUB(UPPER(%FieldDisplayPicture),1,2))
    #OF('@D')
      #SET(%ValType,'date')
    #OF('@T')
      #SET(%ValType,'time without time zone')
    #OF('@S')
      #SET(%ValType,'text')
      #IF(%ValLength > 255)
        #SET(%ValType,'text')
      #ENDIF
    #OF('@N')
      #SET(%ValType,'integer')
      #IF(%ValDecimal > 0)
        #SET(%ValType,'numeric')
      #ENDIF
    #ENDCASE
    #!================================!
    #!================================!
    #IF(ITEMS(%FieldValues) > 0)
      #FOR(%FieldValues)
        #CASE(%ValType)
        #OF('text')
          #ADD(%ValList,'''' & %FieldValues & '''')
        #ELSE
          #ADD(%ValList,%FieldValues)
        #ENDCASE
      #ENDFOR
    #ENDIF
    #!=================================!
    #! Description setup happens here. !
    #!=================================!
    #CASE(%FieldValidation)                                         #! PREFIX
    #OF  ('NONZERO')                                                #! NULL / NOT NULL
    #OF  ('INFILE')
    #OROF('BOOLEAN')
    #OROF('INRANGE')
    #OROF('INLIST')
CREATE OR REPLACE FUNCTION %NFileName_%NFieldName_description (param %ValType)
  RETURNS text AS $$
DECLARE
BEGIN
    #ENDCASE
#!
    #CASE(%FieldValidation)                                         #! LOGIC
    #OF  ('NONZERO')                                                #! NULL / NOT NULL
    #OF  ('INFILE')
-- INFILE
    #OF  ('BOOLEAN')
-- BOOLEAN
  RETURN CASE(LOWER(TRIM(param))
    WHEN 'y' THEN 'Yes'
    WHEN '1' THEN 'Yes'
    WHEN 't' THEN 'Yes'
    WHEN 'n' THEN 'No'
    WHEN '0' THEN 'No'
    WHEN 'f' THEN 'No'
    ELSE ''
  END;
    #OF  ('INRANGE')
-- INRANGE
    #OF  ('INLIST')
-- INLIST
      #CASE(%ValType)
      #OF('text')
  IF LENGTH(param) > %ValLength THEN
    RETURN '';
  END IF;
      #ENDCASE
      #LOOP,FOR(%ValI,1,ITEMS(%ValList))
        #SELECT(%FieldValues, %ValI)
        #SELECT(%FieldChoices, %ValI)
-- %FieldValues = %FieldChoices
      #ENDLOOP
      #IF(ITEMS(%ValList) > 0)
  RETURN CASE param
      #LOOP,FOR(%ValI,1,ITEMS(%ValList))
        #SELECT(%FieldValues, %ValI)
        #SELECT(%FieldChoices, %ValI)
        #CASE(%ValType)
        #OF('text')
    WHEN '%FieldValues' THEN '%FieldChoices'
        #ELSE
    WHEN %FieldValues THEN '%FieldChoices'
        #ENDCASE
      #ENDLOOP
    ELSE ''
  END;
      #ENDIF
    #ENDCASE
#!
    #CASE(%FieldValidation)                                         #! POSTFIX
    #OF  ('NONZERO')                                                #! NULL / NOT NULL
    #OF  ('INFILE')
    #OROF('BOOLEAN')
    #OROF('INRANGE')
    #OROF('INLIST')
  RETURN '';
END;
$$ LANGUAGE plpgsql
;
COMMENT ON FUNCTION %NFileName_%NFieldName_description (param %ValType) IS
'TABLE %NFileName FIELD %NFieldName DESCRIPTION.'
;

    #ENDCASE
    #!================================!
    #! Validation setup happens here. !
    #!================================!
    #CASE(%FieldValidation)                                         #! PREFIX
    #OF  ('NONZERO')                                                #! NULL / NOT NULL
    #OF  ('INFILE')
    #OROF('BOOLEAN')
    #OROF('INRANGE')
    #OROF('INLIST')
CREATE OR REPLACE FUNCTION %NFileName_%NFieldName_validate (param %ValType)
  RETURNS boolean AS $$
DECLARE
BEGIN
    #ENDCASE
#!
    #CASE(%FieldValidation)                                         #! LOGIC
    #OF  ('NONZERO')                                                #! NULL / NOT NULL
    #OF  ('INFILE')
-- INFILE
    #OF  ('BOOLEAN')
-- BOOLEAN
      #CASE(%ValType)
      #OF('text')
        #SET(%ValTrue, '''' & %ValTrue & '''')
        #SET(%ValFalse, '''' & %ValFalse & '''')
  IF LENGTH(param) > %ValLength THEN
    RETURN FALSE;
  END IF;
      #ENDCASE
  IF    param = %ValTrue THEN RETURN TRUE;
  ELSIF param = %ValFalse THEN RETURN TRUE;
  END IF;
    #OF  ('INRANGE')
-- INRANGE
    #OF  ('INLIST')
-- INLIST
      #CASE(%ValType)
      #OF('text')
  IF LENGTH(param) > %ValLength THEN
    RETURN FALSE;
  END IF;
      #ENDCASE
      #LOOP,FOR(%ValI,1,ITEMS(%ValList))
        #SELECT(%FieldValues, %ValI)
        #SELECT(%FieldChoices, %ValI)
-- %FieldValues = %FieldChoices
      #ENDLOOP
      #IF(ITEMS(%ValList) > 0)
  RETURN CASE param
        #FOR(%ValList)
    WHEN %ValList THEN TRUE
        #ENDFOR
    ELSE FALSE
  END;
      #ENDIF
    #ENDCASE
#!
    #CASE(%FieldValidation)                                         #! POSTFIX
    #OF  ('NONZERO')                                                #! NULL / NOT NULL
    #OF  ('INFILE')
    #OROF('BOOLEAN')
    #OROF('INRANGE')
    #OROF('INLIST')
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql
;
COMMENT ON FUNCTION %NFileName_%NFieldName_validate (param %ValType) IS
'TABLE %NFileName FIELD %NFieldName VALIDATION.'
;

      #IF(%SQLValidCheck)
ALTER TABLE %NFileName ADD CONSTRAINT bad_%NFileName_%NFieldName
  CHECK (%NFileName_%NFieldName_validate(%NFieldName))
;

      #ENDIF
    #ENDCASE
  #ENDFOR
#ENDFOR
--
-- EOF: %OutValidName
--
  #CLOSE(%OutValidName)
#ENDIF
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#IF(%SQLDateValid)
  #SET(%OutDatesName,%SQLDirectory & %SQLDatesName)
  #CREATE(%OutDatesName)
  #MESSAGE('The POSTGRESQL Script Creator',0)
  #MESSAGE('Creating ' & %SQLDatesName,1)
--
-- BOF: %OutDatesName
--
-- SQL script creator for POSTGRESQL database [Date Procedures and Triggers]
-- Created: %TheDate - %TheTime
--
 #FOR(%ProcFile)
   #FIX(%File,%ProcFile)
   #SET(%DateTrigger,%ProcFile)
  #!---------------------------------------------------------------!
  #! RA.2001.12.01 - Ignore file place marker definitions.         !
  #! These are files that are defined but that contain no fields.  !
  #!---------------------------------------------------------------!
  #IF(NOT ITEMS(%Field))                                        #! RA.2001.12.01
    #CYCLE                                                      #! RA.2001.12.01
  #ENDIF                                                        #! RA.2001.12.01
  #SET(%nLoc1,INSTRING('RASQL(',%FileUserOptions,1,1))
  #SET(%nLoc2,INSTRING(')',%FileUserOptions,1,%nLoc1))
  #SET(%nFileUser,UPPER(SUB(%FileUserOptions,%nLoc1,%nLoc2)))
  #!SET(%NFileUser,UPPER(EXTRACT(%FileUserOptions,'RASQL')))
  #SET(%NFileOpt1,EXTRACT(%NFileUser,'RASQL',1))
  #SET(%NFileOpt2,EXTRACT(%NFileUser,'RASQL',2))
  #SET(%NFileOpt3,EXTRACT(%NFileUser,'RASQL',3))               #! Option to let template know if to generate edit date/time
  #SET(%NFileOpt4,EXTRACT(%NFileUser,'RASQL',4))               #! Option to let template know if to generate create date/time
  #!-------------------------------------------------------------!
  #! The first option RASQL(NO) controls if a file is processed. !
  #!-------------------------------------------------------------!
  #IF(SUB(%NFileOpt1,1,1)='N')
    #CYCLE
  #ENDIF
#!
  #IF(SUB(%NFileOpt3,1,1) = 'N' and SUB(%NfileOpt4,1,1) = 'N')  #! File has neither create date/time or edit date/time columns defined.
    #CYCLE
  #ENDIF
#!
  #CASE(%FileType)
  #OF  ('VIEW')
  #OROF('ALIAS')
      #CYCLE
  #ENDCASE
#!
  #CASE(%FileDriver)
    #OF('ASCII')
  #OROF('BASIC')
  #OROF('DOS')
  #OROF('IN-MEMORY')
      #CYCLE
  #ENDCASE
#!
  #SET(%NFileOpt0,'')
  #SET(%nLoc1,INSTRING('EMULATEAUTONUMKEY(',UPPER(%FileUserOptions),1,1))
  #SET(%nLoc2,INSTRING(')',%FileUserOptions,1,%nLoc1))
  #SET(%nFileUser,UPPER(SUB(%FileUserOptions,%nLoc1,%nLoc2)))
  #SET(%NFileOpt0,EXTRACT(%NFileUser,'EMULATEAUTONUMKEY',1))
#!
  #MESSAGE('Table of: ' & %DateTrigger,2)

#!
#INSERT(%PGModifyNameTable)
#!
#SET(%DateTrigger,lower(%DateTrigger))


CREATE FUNCTION %DateTrigger_stamp() RETURNS trigger AS $%DateTrigger_stamp$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
          ELSIF (TG_OP = 'UPDATE') THEN
            NEW.%EditDateTime := current_timestamp;
            NEW.%EditUser     := current_user;
            RETURN NEW;
          ELSIF (TG_OP = 'INSERT') THEN
           #IF(SUB(%NFileOpt4,1,1) = '')
            NEW.%CreateDateTime := current_timestamp;
            NEW.%CreateUser     := current_user;
           #ENDIF
            NEW.%EditDateTime := current_timestamp;
            NEW.%EditUser     := current_user;
            RETURN NEW;
        END IF;
    END;
$%DateTrigger_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER %DateTrigger_stamp BEFORE INSERT OR UPDATE ON %NFileName
    FOR EACH ROW EXECUTE PROCEDURE %DateTrigger_stamp();


#ENDFOR
--
-- EOF: %OutDatesName
--
  #CLOSE(%OutDatesName)
#ENDIF
#!---------------------------------------------------------------------
#! RA MARKER - SEARCH FOR ME
#!---------------------------------------------------------------------
#IF(%SQLTable)
  #RUN('NOTEPAD.EXE "' & %OutTableName & '"')
#ENDIF
#IF(%SQLGrant)
  #RUN('NOTEPAD.EXE "' & %OutGrantName & '"')
#ENDIF
#IF(%SQLIndex)
  #RUN('NOTEPAD.EXE "' & %OutIndexName & '"')
#ENDIF
#IF(%SQLRelat)
  #RUN('NOTEPAD.EXE "' & %OutRelatName & '"')
#ENDIF
#IF(%SQLValid)
  #RUN('NOTEPAD.EXE "' & %OutValidName & '"')
#ENDIF
#IF(%SQLDateValid)
  #RUN('NOTEPAD.EXE "' & %OutDatesName & '"')
#ENDIF
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#GROUP(%Dct2POSTGRESQLMichaelGouldEnd)
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------
#!---------------------------------------------------------------------