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: [email protected] [mailto:[email protected]] On
Behalf Of Pavel Stehule
Sent: 30 Agustus 2009 22:56
To: Tom Lane
Cc: Yogi Rizkiadi; [email protected]
Subject: Re: [SQL] NEED HELP COPY TO DYNAMIC OUTPUT FILE
2009/8/30 Tom Lane <[email protected]>:
> Pavel Stehule <[email protected]> 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 ([email protected])
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
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
