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
Our community of experts have been thoroughly vetted for their expertise and industry experience.