CODEWITHSUNDEEP
postgresqlplpgsql
Jacobian
Jacobian

Reputation: 10802

How to execute delete statement inside a function

I want clients of my application to call DELETE statements indirectly or using a function. This is what I tried:

CREATE OR REPLACE FUNCTION layer_250_delete(layer_id integer)
    RETURNS bool AS
$BODY$ 
BEGIN    
    EXECUTE 'DELETE FROM layer_250_ WHERE id = $1' USING layer_id;    
    RETURN TRUE;   
END; 
$BODY$
   LANGUAGE plpgsql VOLATILE
   COST 100;
ALTER FUNCTION layer_250_delete(integer)
OWNER TO postgres;

I also tried a raw query:

...
DELETE FROM layer_250_ WHERE id = 1';    
RETURN TRUE;
...

However, when I call this function (variant 1 or 2) like this:

select layer_250_delete(1);

it does not delete a row from layer_250_ table. I scanned dozens of threads here at stackoverflow, but could not find anything similar to my task.

STRUCTURE

CREATE TABLE public.layer_250_
(
  id integer NOT NULL DEFAULT nextval('layer_250__id_seq'::regclass),
  feature_type character varying(100) NOT NULL,
  feature_hash character varying(500) NOT NULL,
  feature_uid character varying(100) NOT NULL,
  geom geometry,
  radius integer,
  group_id integer,
  object_id integer NOT NULL DEFAULT 0,
  row_id integer NOT NULL DEFAULT 0,
  action_time timestamp without time zone NOT NULL DEFAULT now(),
  action_type character varying(255),
  action_user_id integer,
  action_user_ip character varying(255),
  CONSTRAINT layer_250__pkey PRIMARY KEY (id),
  CONSTRAINT layer_250__feature_uid_key UNIQUE (feature_uid),
  CONSTRAINT enforce_dims_geom_layer_250_ CHECK (st_ndims(geom) = 2),
  CONSTRAINT enforce_srid_geom_layer_250_ CHECK (st_srid(geom) = 3857)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.layer_250_
  OWNER TO postgres;

Upvotes: 0

Views: 3925

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51446

Yrs, it does:

  t=# create table layer_250_ (id int);
    CREATE TABLE
    t=# insert into layer_250_ select 1;
    INSERT 0 1
    t=# CREATE OR REPLACE FUNCTION layer_250_delete(layer_id integer)
    t-#     RETURNS bool AS
    t-# $BODY$
    t$# BEGIN
    t$#     EXECUTE 'DELETE FROM layer_250_ WHERE id = $1' USING layer_id;
    t$#     RETURN TRUE;
    t$# END;
    t$# $BODY$
    t-#    LANGUAGE plpgsql VOLATILE
    t-#    COST 100;
    CREATE FUNCTION
    t=# ALTER FUNCTION layer_250_delete(integer)
    t-# OWNER TO postgres;
    ALTER FUNCTION
    t=# begin;
    BEGIN
    t=# select layer_250_delete(1);
     layer_250_delete
    ------------------
     t
    (1 row)

    t=# select * from layer_250_;
     id
    ----
    (0 rows)

    t=# end;
    COMMIT

Upvotes: 1

Related Questions