Postgresql delete action for copy command

Question

I have this code

create or replace function foo(text)returns int as $$ begin
copy (select * from (delete from bar a where a.id not in(select b.id from bar b where a.id=b.id limit 32 offset 0) returning *) as g) to '/home/foo/.bar.data';
return 1;
end $$ language plpgsql cost 50;

but it cannot create the function and gives the following error

ERROR:  syntax error at or near "from"
LINE 4: copy(select * from(delete from bar a ...
                                  ^
********** Error **********
ERROR: syntax error at or near "from"
SQL state: 42601
Character: 29

The delete command (delete from bar ...) is prepared and run without any problem when I call it alone.
I also tried this(without select), same error.

copy (delete from bar a where a.id not in(select b.id from bar b where a.id=b.id limit 32 offset 0) returning *) to '/home/foo/.bar.data';

All I want is delete from the table and copy to a file.
Also how may I have void method in pgsql?

score 0 · Answer 1 · edited May 23 '17 at 12:12

Check out this: Using return value from DELETE for UPDATE in Postgres

The accepted answer there may point you in the right direction. It appears there is some inherent PostgreSQL limitation surrounding the use of DELETE in a subquery, which may extend to COPY. The linked question specifically discusses the complications of using DELETE results in an UPDATE, but the workaround provided may work for your use case as well.

score 0 · Answer 2 · answered Dec 15 '14 at 07:46

You cannot use COPY like that, as the syntax doesn't support using a subquery. And the file_fdw is read-only insofar as I'm aware, so that would would work either.

What might work instead, is something like:

create temporary table ... as
delete from ... returning *;

... and then copy the temporary table to a file.

Postgresql delete action for copy command

2 Answers2