I'm fairly new at PostgreSQL and I created a simple table with insert function but when I use my delete function it erase all my inserted queries any idea what might happen? I'm also using PostgreSQL v9.6 if anyone is asking
CREATE TABLE sample(
id SERIAL PRIMARY KEY,
sy char(9),
sem char(1)
);
CREATE FUNCTION insert(sy char,sem char)
returns void
AS
$$
Begin
insert into sample(sy,sem) values (sy,sem);
END;
$$
LANGUAGE 'plpgsql';
select insert('2011-2012','1');
select insert('2013-2014','2');
select insert('2015-2016','1');
select insert('2017-2018','2');
CREATE FUNCTION delete(id int)
returns bool
AS
$$
BEGIN
EXECUTE 'delete from sample where id = id';
RETURN true;
END;
$$
LANGUAGE 'plpgsql';
select delete(1);
select * from sample
id sy sem
delete from sample where id = id
deletes all rows where the column id
equals the column id
. As this is all part of a string no parameter replacement is taking place. But there is no need to use dynamic SQL (execute '...'
) to begin with. To avoid a clash between column names and parameter names it's recommended to distinguish them somehow. Many people to that by using a prefix for the parameter. Also: the language name is an identifier and should not be quoted. That syntax is deprecated and might be removed in a future version, so don't get used to it.
So, get rid of the dynamic SQL and rename your parameter:
CREATE FUNCTION delete(p_id int)
returns bool
AS
$$
BEGIN
delete from sample where id = p_id;
return true;
END;
$$
LANGUAGE plpgsql;