How to delete a row in a stored procedure in PostgreSQL with dynamic table and column names?
delete_word will be the one that should be deleted.

CREATE OR REPLACE PROCEDURE delete_row(tablename VARCHAR(255),columnname VARCHAR(255), delete_word VARCHAR(255))
language plpgsql
as $$
BEGIN
    EXECUTE 'DELETE FROM ' || quote_ident(tablename) || ' WHERE ' || columnname || ' = ' || quote_ident(delete_word);
END $$; 

CALL delete_row('sales_2019','orderid', 'Order ID');

CodePudding user response：

Handling dynamic SQL properly:

CREATE OR REPLACE PROCEDURE delete_row(tablename text, columnname text, delete_word text)
  LANGUAGE plpgsql AS
$proc$
DECLARE
   _sql text := format('DELETE FROM %I WHERE %I = $1', $1, $2, $3);
BEGIN
   -- RAISE NOTICE '%', _sql;  -- debug first?!
   EXECUTE _sql
   USING   delete_word;
END
$proc$;

Call:

CALL delete_row('sales_2019', 'orderid', 'Order ID');

See:

• Format specifier for integer variables in format() for EXECUTE?
• Table name as a PostgreSQL function parameter
• How to use variable as table name in plpgsql

Dynamic SQL with format() and EXECUTE is only needed if table and column name must indeed be variable. The answer is a proof of concept for more complex tasks. A simple command like in the example, I would rather just concatenate and execute as plain SQL.

Also using data type text for arguments. varchar(255) does not do anything useful there, and generally tends to be a misunderstanding carried over from other RDBMS. See:

• Refactor foreign key to fields