Function to DELETE rows taking the parameters in sql query

Question

I want create a function which removes rows and returns true if there are no errors:

CREATE OR REPLACE FUNCTION s_grnplm_tt.sp_del_stts_cd(num text, package_name text, stts_cd text)
      returns bool
      LANGUAGE plpgsql as 
$func$ 
BEGIN
         EXECUTE 
     'delete FROM s_grnplm_'||num||'tt.log_copy d WHERE d.package_name =' ||package_name|| ' and d.stts_cd = $1'
      
      using stts_cd;
      return true;
end;
$func$;

I'm calling a function: select s_grnplm_tt.sp_del_stts_cd('089', '0202_20232211_2434', 'U')

but I get an error: SQL Error [42601]: ERROR: syntax error at or near "_20232211_2434"Where: PL/pgSQL function sp_del_stts_cd(text,text,text) line 3 at EXECUTE statement

Answer 1

First, you should to read some about SQL injection issue. You never to pass string variables to SQL just by append || operator. You should to sanitize string values before. It can be done by usage function format and related placeholders or by using functions quote_ident and quote_literal.

Second, when you have problem with dynamic SQL (EXECUTE) statement, then you should to use RAISE NOTICE statement and display executed SQL statement. Then the issue is clean:

delete FROM s_grnplm_089tt.log_copy d WHERE d.package_name =0202_20232211_2434 and d.stts_cd = $1

You are missing apostrophes in predicate d.package_name =0202_20232211_2434.

Your function can be rewritten to:

CREATE OR REPLACE FUNCTION s_grnplm_tt.sp_del_stts_cd(num text,
                                                      package_name text,
                                                      stts_cd text)
      RETURNS void
      LANGUAGE plpgsql as 
$func$ 
DECLARE sqlstr text;
BEGIN
  sqlstr := format('DELETE FROM %I d WHERE d.package_name = %L AND d.stts_cd = $1',
                   's_grnplm_' || num || 'tt.log_copy', package_name);
  RAISE NOTICE '%', sqlstr;
  EXECUTE sqlstr USING stts_cd;
END;
$func$;

Now, the query is correct:

DELETE FROM "s_grnplm_089tt.log_copy" d WHERE d.package_name = '0202_20232211_2434' AND d.stts_cd = $1

The dynamic SQL requires passing only SQL identifiers as strings. Others can be passed binary by usage USING clause. So predicate d.package_name = should be used similarly to d.stts_cd = predicate:

BEGIN
  sqlstr := format('DELETE FROM %I d WHERE d.package_name = $1 AND d.stts_cd = $2',
                   's_grnplm_' || num || 'tt.log_copy');
  RAISE NOTICE '%', sqlstr;
  EXECUTE sqlstr USING package_name, stts_cd;
END;