PeopleCode | Using PSRECDEFN or Project Definitions to perform SQL deletes
A. PSRECDEFN
You may have a requirement that asks you to delete records from a set of tables, all with the same key value (eg for a certain Employee ID). It might seem like the only approach is to go through all the tables one-by-one, performing the delete, but if the tables all belong to the same piece of functionality – or form part of the same project – there is a quick way to perform a bulk delete using the PeopleTools table PSRECDEFN.
Like any requirement that involves performing a delete operation, care must be taken to properly test the fix, preferably in a non-Production database.
B. Deletes based on Record Names
If all the records have a similar naming convention, the following piece of code can be used to delete for a single key value, Employee ID in this case. The RECTYPE = 0 clause in the select ensures that only database tables are selected (not views or derived work records, etc).
/* Select matching records with a prefix of XX_STG */
&RecordsToDelete_RS = CreateRowset(Record.PSRECDEFN);
&RecordsToDelete_RS.Fill("where RECNAME like 'XX_STG%' and RECTYPE = 0");
For &i = 1 To &RecordsToDelete_RS.ActiveRowCount
&RecordsToDelete_Rec = &RecordsToDelete_RS.GetRow(&i).PSRECDEFN;
&sql_delete = "delete from ps_" | &RecordsToDelete_Rec.RECNAME.Value | " where EMPLID = :1";
SQLExec(&sql_delete, "1234567");
End-For;
C. Deletes based on Project Definition
If all the records to delete are located in a single project, you can run the code below to perform the bulk delete. Take care that the project contains ONLY the records to be deleted and not other records, otherwise this code may not work correctly. It might, for instance, try to delete from a table that doesn’t have an EMPLID, which will throw a fatal error. In the code below, replace XXXXX with the name of your project:
/* Select matching records from the XXXXX project */
&RecordsToDelete_RS = CreateRowset(Record.PSPROJECTITEM);
&RecordsToDelete_RS.Fill("where OBJECTTYPE = 0 and PROJECTNAME = 'XXXXX' and exists (select 'x' from PSRECDEFN A where A.RECNAME = FILL.OBJECTVALUE1 and A.RECTYPE = 0)");
For &i = 1 To &RecordsToDelete_RS.ActiveRowCount
&RecordsToDelete_Rec = &RecordsToDelete_RS.GetRow(&i).PSPROJECTITEM;
&sql_delete = "delete from ps_" | &RecordsToDelete_Rec.OBJECTVALUE1.Value | " where EMPLID = :1";
SQLExec(&sql_delete, "1234567");
End-For;
See Also