How to Programmatically Check If Row Is Deletable

How to programmatically check if row is deletable?

You can try to delete the row and roll back the effects. You wouldn't want to do that in a trigger function because any exception cancels all persisted changes to the database. The manual:

When an error is caught by an EXCEPTION clause, the local variables of
the PL/pgSQL function remain as they were when the error occurred, but
all changes to persistent database state within the block are rolled back.

Bold emphasis mine.

But you can wrap this into a separate block or a separate plpgsql function and catch the exception there to prevent the effect on the main (trigger) function.

CREATE OR REPLACE FUNCTION f_can_del(_id int)
  RETURNS boolean AS 
$func$
BEGIN
   DELETE FROM master WHERE master_id = _id; -- DELETE is always rolled back

   IF NOT FOUND THEN
      RETURN NULL;                        -- ID not found, return NULL
   END IF;

   RAISE SQLSTATE 'MYERR';                -- If DELETE, raise custom exception

   EXCEPTION
   WHEN FOREIGN_KEY_VIOLATION THEN
      RETURN FALSE;
   WHEN SQLSTATE 'MYERR' THEN
      RETURN TRUE;
   -- other exceptions are propagated as usual
END  
$func$ LANGUAGE plpgsql;

This returns TRUE / FALSE / NULL indicating that the row can be deleted / not be deleted / does not exist.

db<>fiddle here

_{Old sqlfiddle}

One could easily make this function dynamic to test any table / column / value.

Since PostgreSQL 9.2 you can also report back which table was blocking.

PostgreSQL 9.3 or later offer more detailed information, yet.

Generic function for arbitrary table, column and type

Why did the attempt on a dynamic function that you posted in the comments fail? This quote from the manual should give a clue:

Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.

It works with GET DIAGNOSTICS:

CREATE OR REPLACE FUNCTION f_can_del(_tbl regclass, _col text, _id int)
  RETURNS boolean AS 
$func$
DECLARE
   _ct int;                              -- to receive count of deleted rows
BEGIN
   EXECUTE format('DELETE FROM %s WHERE %I = $1', _tbl, _col)
      USING _id;                         -- exception if other rows depend

   GET DIAGNOSTICS _ct = ROW_COUNT;

   IF _ct > 0 THEN
      RAISE SQLSTATE 'MYERR';            -- If DELETE, raise custom exception
   ELSE
      RETURN NULL;                       -- ID not found, return NULL
   END IF;

   EXCEPTION
   WHEN FOREIGN_KEY_VIOLATION THEN
      RETURN FALSE;
   WHEN SQLSTATE 'MYERR' THEN
      RETURN TRUE;
   -- other exceptions are propagated as usual
END  
$func$ LANGUAGE plpgsql;

db<>fiddle here

_{Old sqlfiddle}

While being at it, I made it completely dynamic, including the data type of the column (it has to match the given column, of course). I am using the polymorphic type anyelement for that purpose. See:

• How to write a function that returns text or integer values?

I also use format() and a parameter of type regclass to safeguard against SQLi. See:

• SQL injection in Postgres functions vs prepared queries

How to check if row is used in database before trying to change it?

Solution

The best way I've came up with is slightly modified first solution. Since there are can be references from several tables, poor first solution (subquery in select clause) sounds bad. I used MyBatis sql tag with parameter.

This element can be used to define a reusable fragment of SQL code
that can be included in other statements. It can be statically
(during load phase) parametrized. Different property values can vary
in include instances

<sql id="columns">
  rt_id,
  rt_code,
  (<include refid="isInUse"><property name="id" value="rt_id"/></include>) AS rt_is_in_use
</sql>

<sql id="baseSelect">
  select
    <include refid="columns"/>

  from relation_type
</sql>

<sql id="isInUse">
  select exists (
    select 1 from entity_relation where er_type = ${id} limit 1
  )
</sql>

<select id="findAll" resultMap="relationTypeMap">
  <include refid="baseSelect"/>
</select>

isInUse sql chunk can also accept MyBatis method parameters. So it's possible to write select

<select id="isInUse"  resultType="boolean">
  <include refid="isInUse">
    <property name="id" value="#{id}"/>
  </include>
</select>

Useful links

• Reusing dynamic sql fragments
• How can I reuse an SQL fragment with parameters?

DataTable, How to conditionally delete rows

You could query the dataset and then loop the selected rows to set them as delete.

var rows = dt.Select("col1 > 5");
foreach (var row in rows)
   { row.Delete(); }
   dt.AcceptChanges();
   

... and you could also create some extension methods to make it easier ...

myTable.Delete("col1 > 5");

public static DataTable Delete(this DataTable table, string filter)
{
    table.Select(filter).Delete();
    return table;
}
public static void Delete(this IEnumerable<DataRow> rows)
{
    foreach (var row in rows)
        row.Delete();
}

I am trying to delete a row of data using a check box, when I click on delete my php should delete the row

Let's put the closed form-tag at the end of your script and name your checkbox : name="checkbox[]"

<form class="searchBar"   method="post" action=''>
  <input type="submit" action='POST'  class="btn-group" id="delete" name="delete" value="delete" style="background-color: #757575; font-family: HelveticaNeue;font-size: 13px;">

  <table class="taskTable"  >
    <tbody class="task-tbody">
        <?php while($row1 = mysqli_fetch_array($Table)){ $task123=$row1[2];?>
        <!-- removed onclick= -->
        <tr class = "task-tr" onclick="myFunction('<?php echo($task123); ?>')">
            <td class="task-td"><input type="checkbox"   name="checkbox[]" value="<?php echo $task123?>"/></td>
            <td class="task-td"> <?php if ($row1[0]=='backlog') {$statuscss= 'statusBacklog';} elseif ($row1[0]== 'inprogress') {$statuscss= 'statusInProgress';} else{ $statuscss= 'statusDone';}    echo '<div class="',$statuscss,'">';?><?php echo $row1[0];?></div></td>
            <td class="task-td"> <?php if ($row1[1]=='HIGH') {$statuscss= 'priorityHigh';} elseif ($row1[1]== 'MEDIUM') {$statuscss= 'priorityMedium ';} else{ $statuscss= 'priorityLow';}    echo '<div class="',$statuscss,'">';?>  <?php echo $row1[1];?></div></td>
            <td class="task-td" > <?php echo $task123;?></td>
            <td class="task-description"> <?php echo $row1[3];?></td>
            <td class="task-td"> <?php echo $row1[4];?></td>
            <td class="task-td"> <?php echo $row1[5];?></td>
            <td class="task-td" width="15"> </td>
        </tr>
        <?php }?>
    </tbody>
  </table>

</form>

Delete specific row from datatable in a repeater

I have a sequence number in the repeater, so I have added the sequence in every record added to the datatable and added it to the the if statement.

the altered code is:

for (int i = GRX.Rows.Count - 1; i >= 0; i--)
                {
                    DataRow dr = GRX.Rows[i];

                    string seq = lbl7.Text;
                    string GRXseq = dr[5].ToString();
                    string GRXseat = dr[4].ToString();

                    if (GRXseat == "R" && lbl4.Text == "2" && GRXseq == seq || GRXseat == "R" && lbl4.Text == "4" && GRXseq == seq)
                    {
                        dr.Delete();

                    }
                }

---

Related Topics