The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,156 other followers

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):

–jeroen

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 ; !!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: