COPY FROM (query) in plpgsql

Hello,

there seems to be no way to use COPY this way, so I guess this is a
feature request...

this may also help users who tried using COPY FROM STDIN in plpgsql.


I have a query with a lot of string manipulation that returns data as
single strings, e.g.:


'a,12,ght,45,1.2'
'b,13,ght,45,1.1'
'a,14,ght,45,1.5'


in order to save this result into a table, I still need to quote the
string value and use EXECUTE:


EXECUTE 'insert into foo values (''a'',12,''ght'',45,1.2)';
EXECUTE 'insert into foo values (''b'',13,''ght'',45,1.1)';
EXECUTE 'insert into foo values (''a'',14,''ght'',45,1.5)';

(or

    EXECUTE 'insert into foo VALUES
     (''a'',12,''ght'',45,1.2),
     (''b'',13,''ght'',45,1.1),
     (''a'',14,''ght'',45,1.5)
     ';
)


I guess this could become faster with such a syntax:

COPY foo FROM
 (
   SELECT 'a,12,ght,45,1.2'
   UNION ALL
   SELECT 'b,13,ght,45,1.1'
   UNION ALL
   SELECT 'a,14,ght,45,1.5'
  ) WITH CSV;


best regards,

Marc Mamin