1

I am trying to create a procedure that will query a table column to retrieve table names and using this in proc to run a query to do a select if it exists. If it does not exist, delete row(s) from systables_growth table.

Below is what I have so far:

CREATE function test_sp();
DEFINE v_tab CHAR(255);
      FOREACH SELECT unique(tabname) INTO v_tab from systables_growth
        BEGIN
            SELECT * FROM v_tab;
            ON EXCEPTION IN (-206) -- If no table was found, delete record from systables_growth table
            DELETE from systables_growth WHERE tabname = 'v_tab'
            END EXCEPTION WITH RESUME;
        END
    END FOREACH
END function;

I am receiving the below syntax error when creating:

CREATE function test_sp();
DEFINE v_tab CHAR(255);
FOREACH SELECT unique(tabname) INTO v_tab from systables_growth
BEGIN
SELECT * FROM v_tab;
ON EXCEPTION IN (-206) -- If no table was found, delete record from systables_growth table
#^
#  201: A syntax error has occurred.
#
DELETE from systables_growth WHERE tabname = 'v_tab'
END EXCEPTION WITH RESUME;
END
END FOREACH
END function;

I am trying to define some logic to query the records pulled from table to run a query doing a select from the table names pulled. If the select says table does not exist then I want the row to be removed from the table. I don't know if I am using the correct logic.

I don't want the procedure to generate any output, I want it to be silent.

1

I found an alternative solution using a stored proc in informix that will also work for me:

create procedure systables_growth_tblclnup()
DELETE FROM systables_growth WHERE tabname NOT IN
(SELECT unique(tabname) FROM systables WHERE systables_growth.tabname = systables.tabname);
end procedure ;
1
  • From this, I can create a trigger before or after insert to execute the proc – Christopher Karsten Aug 27 '19 at 10:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.