postgresqlpostgres-9.6

create simple delete function postgresql


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

Solution

  • 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;