From:
                                                                        1:01
Subject: Re: PL/SQL QuestionTo: INTERBASE@megan.mers.com

At 10:50 AM 1/27/99 -0500, Rebekah Lepro wrote:
>Is it possible to write a procedure that can save information to a
>file?

Yes, as long as the file is defined as an external table.

>I would like to generate a script dynamically based on information in
>the system tables.  Where can I get more information on this topic?

Probably this list.  Generally speaking, I use WISQL to deal with
problems like this, writing the output to a file.  In the case
you're interested in, you'll need a procedure because the number of
index segments is variable.

>My final goal is to build a procedure that would look through the
>RDB$Relation_constraints table to find a primary key, for each of
>the primary keys, grab the related records in the Rdb$index_segments
>table and then generate a statement to recreate that PK as a stand-alone
>statement rather than as part of the table creation ddl that is created
>with Extract Database option of ISQL.

I'm going to give you an example of a stored procedure that does what
you want, but first, let me ask you why in the world you want to do
that?  In earlier messages, you mentioned that you've had trouble
reactivating indexes when you restore a database with indexes inactive.
That option - restoring withou building indexes - should be used only
to restore backups of mildly corrupt databases - ones that have duplicate
values in unique indexes, for example.  You shouldn't use it normally.
Gbak creates indexes just as quickly as you can - and without your
having to do any work at all.

How do you end up with entries in RDB$RELATION_CONSTRAINTS for primary
keys without having the indexes themselves defined?  If you're using
gbak -inactive, the index definitions all exist, all you have to do is
set them to be active.  A WISQL script like this:

     select 'alter index ' || rdb$index_name || ' active;'
          from rdb$relation_constraints
          where c.rdb$relation_name = <my table>
            and c.rdb$constraint_type = 'PRIMARY KEY';

<my table> must be in UPPER case and surrounded with single quotes. To
recreate all of them, leave out the relation name.

It's possible, I guess, that you're trying to control the names of the
primary key indexes, or create duplicate of them.  I suspect that
InterBase is going to fight back if you try to control the names of the
indexes.  The constraint and dependency tables are held together with
triggers and they're not very flexible about changes.

Well, whatever.  You want to read the rdb$relation_constraints table
and create new primary key indexes.  I'll give you two procedures.
The first one does it the "right" way, by declaring primary key
constraints.  The second one does more or less what you asked for,
creating indexes directly.

First you'll need to create an external table.  This is the syntax I
used:
     create table output external file 'd:\harrison\output.txt'
          (output_txt char (250));

Then create this procedure:

   create procedure pk_builder returns (counter integer)
   as
   declare variable  c_name char (31);
   declare variable  r_name char (31);
   declare variable  i_name char (31);
   declare variable  s_name char (31);
   declare variable  output_text varchar (250);
   declare variable  comma varchar(2);
   begin
      counter = 0;
      for select rdb$constraint_name, rdb$relation_name, rdb$index_name
              from rdb$relation_constraints
              where rdb$constraint_type = 'PRIMARY KEY'
              into :c_name, :r_name, i_name
      do begin
            output_text = 'alter table ' || r_name || ' add constraint '
                      || c_name || ' primary key (';
            comma = ' ';
            for select rdb$field_name
                     from rdb$index_segments
                     where rdb$index_name = :i_name
                     order by rdb$field_position
                     into :s_name
           do begin
                     output_text = output_text || comma || :s_name;
                     comma = ', ';
           end
           output_text = output_text || ');';
           insert into output values (:output_text);
           counter = counter +1;
     end
   end

At the end the file 'output.txt' will contain the script.

Basically it's a nested loop that finds all the primary key constraints
and generates the first part of the alter table statement. When it gets
to the column list, it starts an inner loop that looks up the column
in the right order.  The quirkiness with the comma variable ... well,
I didn't ever advocate SQL as a language for mechanical code generation,
now did I?

To do the same thing creating indexes directly, use the same output file
but this procedure:

   create procedure pk_builder returns (counter integer)
   as
   declare variable  r_name char (31);
   declare variable  i_name char (31);
   declare variable  s_name char (31);
   declare variable  output_text varchar (250);
   declare variable  comma varchar(2);
   begin
      counter = 0;
      for select rdb$index_name, rdb$relation_name
              from rdb$relation_constraints
              where rdb$constraint_type = 'PRIMARY KEY'
              into :i_name, r_name
      do begin
            output_text = 'create unique index ' || i_name ||
                     ' on ' || r_name || ' (';
            comma = ' ';
            for select rdb$field_name
                     from rdb$index_segments
                     where rdb$index_name = :i_name
                     order by rdb$field_position
                     into :s_name
           do begin
                     output_text = output_text || comma || :s_name;
                     comma = ', ';
           end
           output_text = output_text || ');';
           insert into output values (:output_text);
           counter = counter +1;
     end
   end

Of course, if you had something else in mind, you could construct your
own index and constraint names from the table name rather than using
the old ones.

But first, I'd worry about why you need to do this when nobody else
does.

Good luck,

Ann

************************************************************************
* InterBase Training Jan 11-15, 1999 - http://www.mers.com/ibt.html    *
* This message came from the list servers at http://www.mers.com       *
* To remove yourself from the list: send an email to listproc@mers.com *
* in the body of the message put: unsubscribe INTERBASE                *
* InterBase Search Engine http://www.mers.com/searchsite.html          *
* InterBase FAQ http://www.mers.com/faqinterbase.html                  *
************************************************************************