DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.id;
	h=Received:X-Yahoo-SMTP:X-YMail-OSG:X-Yahoo-Newman-Property:From:To:Cc:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:Content-Transfer-Encoding:X-Mailer:Thread-Index:Content-Language;
	b=sCgEXURRM8SHIGq6MFwJ+GyGq2Y20QBzeF9y8P8w1+W7p4ZxPI4+0tINy8/JOaMHrEQwrbzMjbAWvYTMd1N9Qq5mlNhQnUIt7z6jJPeXTT4dqz82mu9Kv0FRvFTv9D3x5Sf/IYA1HjNNNsX72SGKZdSzYJilPTnidhVnsUJaOBM=
	;
From: "Yogi Dwianandono Rizkiadi" <gie05tech@yahoo.co.id>
To: "'Pavel Stehule'" <pavel.stehule@gmail.com>,
	"'Tom Lane'" <tgl@sss.pgh.pa.us>
Cc: <pgsql-sql@postgresql.org>
References: <797097.88507.qm@web76214.mail.sg1.yahoo.com>	
	<162867790908292351g14087219r970576b9006fb91b@mail.gmail.com>	
	<13182.1251647634@sss.pgh.pa.us>
	<162867790908300856u1eb20f66sfeec34a52ab6db03@mail.gmail.com>
In-Reply-To: <162867790908300856u1eb20f66sfeec34a52ab6db03@mail.gmail.com>
Subject: Re: NEED HELP COPY TO DYNAMIC OUTPUT FILE
Date: Mon, 31 Aug 2009 12:47:34 +0700
Message-ID: <002d01ca29fe$8a4af280$9ee0d780$@co.id>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AcopinnZ1l1+dlPqQyKx+dPVIteujQAchpow
Content-Language: id

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:=3D0; j:=3D0; k:=3D0;
    LOOP
	FETCH rec_cur INTO v_settlement;
        EXIT WHEN NOT FOUND;
	INSERT INTO log_settlement(log) VALUES(v_settlement);
        IF (i=3Drec_max) then=20
	    STATEMENT:=3D 'copy log_settlement to ''/usr/proj/' || replace( =
current_date, '-' , '') || lpad(text(k),5,'00000') || '.txt''';=20
	    EXECUTE (STATEMENT);
	    TRUNCATE TABLE log_settlement;
	    i:=3D0;
	ELSE
	    i:=3Di+1;
	END IF;
	k:=3Dk+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

--=20
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