Postgres Pro
Downloads
Home   >   mailing lists

Thread: COPY FROM (query) in plpgsql

COPY FROM (query) in plpgsql

From
"Marc Mamin"
Date:
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

Re: COPY FROM (query) in plpgsql

From
Vincent Veyron
Date:
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

Re: COPY FROM (query) in plpgsql

From
Jasen Betts
Date:
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