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