Informix create procedure to query records and drop if value does not exist using select

Question

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.

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