pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNEED HELP COPY TO DYNAMIC OUTPUT FILE
5+ messages / 4 participants
[nested] [flat]
* NEED HELP COPY TO DYNAMIC OUTPUT FILE
@ 2009-08-29 22:02 Yogi Rizkiadi <gie05tech@yahoo.co.id>
0 siblings, 1 reply; 5+ messages in thread
From: Yogi Rizkiadi @ 2009-08-29 22:02 UTC (permalink / raw)
To: pgsql-sql
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
Coba Yahoo! Messenger 9.0 baru. Lengkap dengan segala yang Anda sukai tentang Messenger! http://id.messenger.yahoo.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: NEED HELP COPY TO DYNAMIC OUTPUT FILE
@ 2009-08-30 06:51 Pavel Stehule <pavel.stehule@gmail.com>
parent: Yogi Rizkiadi <gie05tech@yahoo.co.id>
0 siblings, 1 reply; 5+ messages in thread
From: Pavel Stehule @ 2009-08-30 06:51 UTC (permalink / raw)
To: Yogi Rizkiadi <gie05tech@yahoo.co.id>; +Cc: pgsql-sql
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!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: NEED HELP COPY TO DYNAMIC OUTPUT FILE
@ 2009-08-30 15:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2009-08-30 15:53 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Yogi Rizkiadi <gie05tech@yahoo.co.id>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: NEED HELP COPY TO DYNAMIC OUTPUT FILE
@ 2009-08-30 15:56 Pavel Stehule <pavel.stehule@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Pavel Stehule @ 2009-08-30 15:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Yogi Rizkiadi <gie05tech@yahoo.co.id>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: NEED HELP COPY TO DYNAMIC OUTPUT FILE
@ 2009-08-31 05:47 Yogi Dwianandono Rizkiadi <gie05tech@yahoo.co.id>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Yogi Dwianandono Rizkiadi @ 2009-08-31 05:47 UTC (permalink / raw)
To: 'Pavel Stehule' <pavel.stehule@gmail.com>; 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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 CURSOR FOR
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_settlement FROM 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;
LOOP
FETCH 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;
CLOSE rec_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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2009-08-31 05:47 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-08-29 22:02 NEED HELP COPY TO DYNAMIC OUTPUT FILE Yogi Rizkiadi <gie05tech@yahoo.co.id>
2009-08-30 06:51 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-08-30 15:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-08-30 15:56 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-08-31 05:47 ` Yogi Dwianandono Rizkiadi <gie05tech@yahoo.co.id>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox