Function to DELETE rows taking the tablename as parameter

Question

I want create a function:

 CREATE OR REPLACE FUNCTION medibv.delAuto(tableName nvarchar(50), columnName nvarchar(100),value 
 nvarchar(100))
 RETURNS void AS

$BODY$ 
begin 
DELETE from tableName  where columnName=value 

end; 
$BODY$

LANGUAGE plpgsql VOLATILE;

I have these parameters: tableName, columnName, value.
I want tableName as table in PostgreSQL.

You don't seem to have got the "please include your PostgreSQL version and format your questions" message. Please read http://stackoverflow.com/editing-help, http://stackoverflow.com/faq, and the info tab of [tag:postgresql] — Craig Ringer, Apr 16 '13 at 05:20

score 3 · Answer 1 · edited Jun 20 '20 at 09:12

CREATE OR REPLACE FUNCTION medibv.delauto(tbl regclass, col text, val text
                                         ,OUT success bool)
  RETURNS bool AS
$func$ 
BEGIN

EXECUTE format('
   DELETE FROM %s
   WHERE  %I = $1
   RETURNING TRUE', tbl, col)
USING   val
INTO    success;

RETURN;  -- optional in this case

END
$func$ LANGUAGE plpgsql;

Call:

SELECT medibv.delauto('myschema.mytbl', 'my_txt_col', 'foo');

Returns TRUE or NULL.

There is no nvarchar type in Postgres. You may be thinking of SQL Server. The equivalent would be varchar, but most of the time you can simply use text.
regclass is a specialized type for registered table names. It's perfect for the case an prevents SQL injection for the table name automatically and most effectively. More in the related answer below.
The column name is still prone to SQL injection. I sanitize the function with format(%I).
format() requires PostgreSQL 9.1+.
Your function did not report what happened. One or more rows may be found and deleted. Or none at all. As a bare minimum I added a boolean OUT column which will be TRUE if one or more rows were deleted. Because (quoting the manual here):

If multiple rows are returned, only the first will be assigned to the INTO variable.
Lastly, use USING with EXECUTE to pass in values. Don't cast back and forth. This is inefficient and prone to errors and to SQLi once more.
Find more explanation and links in this closely related answer:
Table name as a PostgreSQL function parameter

score 1 · Answer 2 · answered Apr 16 '13 at 05:21

1

Use EXECUTE to run dynamic commands:

CREATE OR REPLACE FUNCTION medibv.delAuto(tableName nvarchar(50), columnName nvarchar(100),value 
 nvarchar(100))
 RETURNS void AS

$BODY$ 
begin 
EXECUTE 'DELETE FROM ' || tableName || ' WHERE ' || columnName || '=' || value; 

end; 
$BODY$

LANGUAGE plpgsql VOLATILE;

answered Apr 16 '13 at 05:21

Eelke

20,897
4
50
76

@LeNgocLoan: This is not valid Postgres syntax (nvarvchar? and you would need to at least quote the value to make it work at all) and one needs to at least address SQL injection here. – Erwin Brandstetter Apr 22 '13 at 02:56
Only in case of Dynamic table names , EXECUTE should be used .Else you can just replace the variable name passed in a Function with the variable passed in SQL Command . CREATE OR REPLACE FUNCTION xyz(abc integer) RETURNS void AS $BODY$ declare --Declare your variables here begin ---DML Operation like Select or Delete DELETE FROM TableNAME Where Condition LIMIT abc); GET DIAGNOSTICS v_cnt = ROW_COUNT; EXCEPTION WHEN others THEN RAISE NOTICE 'Error Occurred '; end; $BODY$ – Ankur Srivastava Feb 25 '17 at 04:43

Function to DELETE rows taking the tablename as parameter

2 Answers2