Re: BUG #18774: Not the required output of the query used in the function(delete_from_table1) in postgresql9.4 - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      18774
Logged by:          Rajni Bobal
Email address:      rajnibobal@gmail.com
PostgreSQL version: Unsupported/Unknown
Operating system:   Ubuntu 22
Description:       

The output or behavior of the query used in the function(delete_from_table1)
below is not producing the expected results, while
function(delete_from_table2)  with its query produces the expected result
when used. ( Means all the data is deleted by the function
delete_from_table1, while data (based on wherecon condition) is deleted by
the function  delete_from_table2.)

CREATE OR REPLACE FUNCTION delete_from_table1(tblname text, sel_tblname
text, wherecon text, colname text, batchsize int)
RETURNS void AS
$$
BEGIN
    -- Construct dynamic SQL for DELETE
    EXECUTE 'DELETE FROM '
        || quote_ident(tblname)
        || ' WHERE '
        || quote_ident(colname)
        || ' IN (SELECT '
        || quote_ident(colname)
        || ' FROM '
        || quote_ident(sel_tblname)
        || ' WHERE '
        || wherecon
        || ' LIMIT '
        || batchsize || ')';
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION delete_from_table2(tblname text, sel_tblname
text, wherecon text, colname text, batchsize int)
RETURNS void AS
$$
BEGIN
    EXECUTE 'DELETE from ' || quote_ident(tblname) ||
        ' where $1 in (select $1 from ' || quote_ident(sel_tblname) ||
        ' where ' || wherecon || ' limit $2 ) '
        USING colname, batchsize;
END;
$$ LANGUAGE plpgsql;