CODEWITHSUNDEEP
postgresql
Simon Su
Simon Su

Reputation: 2343

How can I get result by using “execute ‘delete from table1'"

when I use execute command to run a sql cmd, I want to get the result of it. As we know, I can get total counts by variable sc when I use :

execute 'select * from table" into sc;

But How can I get result by using:

execute 'delete from table1'"?

when I use INTO, it turns out 

ERROR: "INTO used with a command that cannot return data"

Upvotes: 1

Views: 1898

Answers (2)

Houari
Houari

Reputation: 5621

execute 'WITH row_deleted AS (DELETE FROM table1 RETURNING *) SELECT count(*) FROM row_deleted' into c;

You can use it inside a plsql funtion as following:

--Drop the table and the functin if it exist:
DROP TABLE IF EXISTS table1;

DROP FUNCTION if exists _deleted_rows();
--Create the table for the example:
CREATE TABLE table1
(
  row_id serial NOT NULL,
  col1 character varying,
  CONSTRAINT table1_pkey PRIMARY KEY (row_id)
);

--Insert some rows:
insert into table1 (col1) values ('test1');
insert into table1 (col1) values ('test2');
insert into table1 (col1) values ('test3');

--Ctreate the function that count the number of deleted rows of the table: table1
CREATE OR REPLACE FUNCTION _deleted_rows()
  RETURNS character varying AS
$BODY$declare 
nbr_deleted  integer;
begin
    execute 'WITH row_deleted AS (DELETE FROM table1 RETURNING *) SELECT count(*) FROM row_deleted' into nbr_deleted;
    return (nbr_deleted);
end;$BODY$
LANGUAGE plpgsql VOLATILE;

Test that function (got problem building shema on sqlfidlle):

select * from _deleted_rows();

 _deleted_rows
---------------
 3
(1 ligne)

Execute command

DELETE command

Upvotes: 2

David S
David S

Reputation: 13851

It's a little unclear to me what you are trying to do, but you should be able use "RETURNING". Here I am just returning the rows that were deleted:

CREATE TEMP TABLE foo(id int, description text);
INSERT INTO foo VALUES
(1, 'HELLO'),
(2, 'WORLD');

DELETE FROM foo returning *;
+----+-------------+
| id | description |
+----+-------------+
|  1 | HELLO       |
|  2 | WORLD       |
+----+-------------+
(2 rows)

Also, if you need them moved "into" a table (for example), you could do something like:

DROP TABLE IF EXISTS foo;
DROP TABLE IF EXISTS deleted_foo;

CREATE TEMP TABLE foo(id int, description text);
INSERT INTO foo VALUES
(1, 'HELLO'),
(2, 'WORLD');

CREATE TEMP TABLE deleted_foo(id int, description text);

WITH x AS (DELETE FROM foo RETURNING *)
INSERT INTO deleted_foo
SELECT * FROM x;

SELECT * FROM deleted_foo;

+----+-------------+
| id | description |
+----+-------------+
|  1 | HELLO       |
|  2 | WORLD       |
+----+-------------+
(2 rows)

Assuming that you are doing this from inside a plpgsql function, you could also use the ROW_COUNT variable. For example:

GET DIAGNOSTICS integer_var = ROW_COUNT;

This would give you the number of rows that were deleted.

Upvotes: 0

Related Questions