create simple delete function postgresql

Question

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

user330315 · Accepted Answer · 2021-02-26 13:21:02Z

1

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;

answered Feb 26, 2021 at 13:21

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

create simple delete function postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related