Обсуждение: 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
Le mardi 23 août 2011 à 11:29 +0200, Marc Mamin a écrit : > 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: > If you can write the data to disk, this gets you rid of the quote problem : COPY query TO 'file.csv'; COPY foo FROM 'file.csv' WITH CSV DELIMITER ','; -- Vincent Veyron http://marica.fr/ Logiciel de gestion des sinistres et des contentieux pour le service juridique
On 2011-08-23, Marc Mamin <M.Mamin@intershop.de> wrote: > 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: you can wrap it up and cast it to foo: insert into foo select(('('|| 'a,12,ght,45,1.2' ||')')::foo).*; -- etc insert into foo select ('(a,12,ght,45,1.2)'::foo).* union all select ('(b,13,ght,45,1.1)'::foo).* union all select ('(a,14,ght,45,1.5)'::foo).*; -- ⚂⚃ 100% natural