I have a c# application running against a postgresql database.
I want to use a delete statement in my stored procedure where
the table name i am deleting from is sent in as parameter This will allow me use the
same script for different tables.
To test the concept, I did a stored procedure with the table name 'locations ' explicitly stated
(see procedure below ) and it worked
1. The script that worked stating table name as 'locations'
[]CREATE OR REPLACE FUNCTION public.spalltables_delete(
IN companycodex text,
IN tablex text,
IN idx integer,
IN deletetypex text)
RETURNS void AS
$BODY$
BEGIN
UPDATE locations
SET
validrecordyn = 'N'
where id = idx and companycode = companycodex and 'DM' = deletetypex;
IF NOT FOUND THEN
delete from locations where id = idx and companycode = companycodex and 'DP' = deletetypex;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.spalltables_delete(text,text,integer,text)
OWNER TO postgres;[/code]
-------------------------------
When I now brought in the table name 'locations 'with a parameter tablex (see below)
i got the error attached
2. The script with table name as a parameter
[][CREATE OR REPLACE FUNCTION public.spalltables_delete(
IN companycodex text,
IN tablex text,
IN idx integer,
IN deletetypex text)
RETURNS void AS
$BODY$
BEGIN
UPDATE tablex
SET
validrecordyn = 'N'
where id = idx and companycode = companycodex and 'DM' = deletetypex;
IF NOT FOUND THEN
delete from tablex where id = idx and companycode = companycodex and 'DP' = deletetypex;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.spalltables_delete(text,text,integer,text)
OWNER TO postgres;[/code]
The script above compiles properly as a stored procedure but give the error below during execution.
3. Error Page

How can i bring in table name as parameter and get it to work in my stored procedure
Olukay
1) Use an IF statement to switch between the possible tables.
2) Use dynamic SQL.
The last is not advisable.