Firebird: creating indices in scripts after checking for their existence
Posted by jpluimers on 2017/07/11
A few notes when making scripts that create indexes based if they do (not) exist (yet):
- when recreating indexes, use with autonomous transaction as shown in http://stackoverflow.com/questions/12117206/how-do-i-make-a-repeatable-index-script/12121998#12121998
- If you screw up the execute statement part, then the error Firebird returns has a line/column is relative to the opening quote of the execute statement string (that quote is at column zero and line one).
- If you screw up the execute block part, then the error line/column is relative to the word execute (it has column 1 and line 1).
- Don’t forget the set term parts to choose a terminator other than the default semicolon (most people use !! or ^ or # as the new terminator) and revert back to the normal semicolon terminator.
–jeroen
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| — when recreating indexes, use `with autonomous transaction`: | |
| — http://stackoverflow.com/questions/12117206/how-do-i-make-a-repeatable-index-script/12121998#12121998 | |
| /* NOTE: | |
| If you screw up the `execute statement` part, then the error line/column is relative to the opening quote | |
| of that statement (it has column zero and line one). | |
| If you screw up the `execute block` part, then the error line/column is relative to the word execute | |
| (it has column 1 and line 1). | |
| */ | |
| set term !! ; | |
| execute block as | |
| begin | |
| if ( | |
| not exists ( | |
| select 1 | |
| from RDB$INDICES indices | |
| where 1=1 | |
| and upper(indices.RDB$INDEX_NAME) = upper('channels_endpoint_index') | |
| ) | |
| ) | |
| then | |
| execute statement 'create index channels_endpoint_index on channels (endpoint)'; | |
| end | |
| !! | |
| set term ; !! |
The Wiert Corner - irregular stream of stuff 
Leave a comment