Thread: NEED HELP COPY TO DYNAMIC OUTPUT FILE

<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top">Hi admin, i'm gie from
indonesia<br/><br />i wanna ask you how to make a dynamic output file from command COPY TO ?<br /><br />i have tried
this:<br /><br />BEGIN<br /> i:=0;<br /> j:=10;<br /> WHILE i < j LOOP<br /> COPY (SELECT * FROM country) TO<span
style="background-color:rgb(255, 255, 255);"> <span style="color: rgb(255, 0, 0);">'/usr/proj/' || i || '.txt'</span>;
//need attention here</span><br /> END LOOP;<br /> RETURN;<br />END<br /><br />but it seems getting wrong, so what the
rightsyntax's ?<br /><br />Sorry for my bad english and thanks in advance<br /><br /></td></tr></table><br /><hr
size="1"/><a
href="http://sg.rd.yahoo.com/id/mail/domainchoice/mail/signature/*http://mail.promotions.yahoo.com/newdomains/id/">
Dapatkanalamat Email baru Anda! </a><br /> Dapatkan nama yang selalu Anda inginkan sebelum diambil orang lain! 

Hello

COPY in plpgsql are not allowed.

regards
Pavel Stehule

2009/8/30 Yogi Rizkiadi <gie05tech@yahoo.co.id>:
> Hi admin, i'm gie from indonesia
>
> i wanna ask you how to make a dynamic output file from command COPY TO ?
>
> i have tried this :
>
> BEGIN
> i:=0;
> j:=10;
> WHILE i < j LOOP
> COPY (SELECT * FROM country) TO '/usr/proj/' || i || '.txt'; // need
> attention here
> END LOOP;
> RETURN;
> END
>
> but it seems getting wrong, so what the right syntax's ?
>
> Sorry for my bad english and thanks in advance
>
>
> ________________________________
> Dapatkan alamat Email baru Anda!
> Dapatkan nama yang selalu Anda inginkan sebelum diambil orang lain!

Pavel Stehule <pavel.stehule@gmail.com> writes:
> COPY in plpgsql are not allowed.

I think it will work if you use an EXECUTE.
        regards, tom lane

2009/8/30 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> COPY in plpgsql are not allowed.
>
> I think it will work if you use an EXECUTE.
>
>                        regards, tom lane
>

I didn't test it.

regards
Pavel Stehule

Thanks guys for the quick reply, i've solve it with using EXECUTE :P

Here is my fully code :

-- Function: etiket_ho.fn_settlement(integer)

-- DROP FUNCTION etiket_ho.fn_settlement(integer);

CREATE OR REPLACE FUNCTION etiket_ho.fn_settlement(integer) RETURNS void AS
$BODY$
DECLARE     i integer;     k integer;     STATEMENT TEXT;     v_settlement TEXT;     rec_max ALIAS FOR $1;     rec_cur
CURSORFOR       SELECT ('0220' || station_id || lpad(trans_id,5,'00000') || lpad(settlement_id,5,'00000') ||
flazz_card_pan|| flazz_card_expired || trans_date || updated_flazz_card_balance || amount_payment || completion_code ||
psam_id|| psam_trans_no || psam_random_no || psam_crytogram || flazz_card_cryptogram || flazz_card_trans_no ||
flazz_card_debit_certificate|| merchant_id || terminal_id || trn || flazz_version || flazz_trac_expired || reserved) AS
val_settlementFROM etiket_ho.t_settlement ORDER BY station_id, trans_id; 
BEGIN   CREATE TEMPORARY TABLE log_settlement (log TEXT);   OPEN rec_cur;   i:=0; j:=0; k:=0;   LOOPFETCH rec_cur INTO
v_settlement;      EXIT WHEN NOT FOUND;INSERT INTO log_settlement(log) VALUES(v_settlement);       IF (i=rec_max) then
  STATEMENT:= 'copy log_settlement to ''/usr/proj/' || replace( current_date, '-' , '') || lpad(text(k),5,'00000') ||
'.txt''';    EXECUTE (STATEMENT);    TRUNCATE TABLE log_settlement;    i:=0;ELSE    i:=i+1;END IF;k:=k+1;   END LOOP;
CLOSErec_cur;   DROP TABLE log_settlement;   RETURN; 
END
$BODY$ LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION etiket_ho.fn_settlement(integer) OWNER TO postgres;

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Pavel Stehule
Sent: 30 Agustus 2009 22:56
To: Tom Lane
Cc: Yogi Rizkiadi; pgsql-sql@postgresql.org
Subject: Re: [SQL] NEED HELP COPY TO DYNAMIC OUTPUT FILE

2009/8/30 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> COPY in plpgsql are not allowed.
>
> I think it will work if you use an EXECUTE.
>
>                        regards, tom lane
>

I didn't test it.

regards
Pavel Stehule

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

__________________________________________________
Apakah Anda Yahoo!?
Lelah menerima spam?  Surat Yahoo! memiliki perlindungan terbaik terhadap spam
http://id.mail.yahoo.com