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"
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
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.
