From gie05tech@yahoo.co.id Sat Aug 29 22:09:14 2009 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 744696332AB for ; Sat, 29 Aug 2009 19:09:14 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 99115-07 for ; Sat, 29 Aug 2009 22:09:06 +0000 (UTC) X-Greylist: delayed 00:06:40.14281 by SQLgrey-1.7.6 Received: from web76214.mail.sg1.yahoo.com (web76214.mail.sg1.yahoo.com [124.108.115.162]) by mail.postgresql.org (Postfix) with SMTP id 92E0A632736 for ; Sat, 29 Aug 2009 19:09:05 -0300 (ADT) Received: (qmail 91187 invoked by uid 60001); 29 Aug 2009 22:02:21 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.co.id; s=s1024; t=1251583341; bh=vpdLmrvFmVZwL1zArILFvPY0JRRb04MRBpHSmRWchkI=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type; b=iRPMBlhb0aeCCCROFtcTNsP4TUDxMmzWaz0ibwx+pglJKYERwfpie5IIEX8NTPXZnGqTazJePf/qb4lPfGZstFX8IlOr8blc/2PDt5z5tv1Dm57yRi1Vne02uB9ME/5iCbZ3njOMQwRFgNmIBf8kPv22WXrCEkDSuWwjZLSBE2E= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.id; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type; b=fpPb46soac5fMREyHDoA2GpxPlJvEfQN4Mz+JjPlZB339LXNQGnc5uuUoMZWxpjeocTD+L56PVzdVu8UWubnt4PxmB7cKFnedJ0IapE43X2Rfjuztq4GjBaHjKsi0/5FnaGDB+La15HWbqJtXxjhzQeKiMXlzpNPehQq13nfJXA=; Message-ID: <797097.88507.qm@web76214.mail.sg1.yahoo.com> X-YMail-OSG: Wuj_KD4VM1kGEqAR7SvuGt.bLus9Lb9c21BnvdXSJqhgncyU0J8nbxE7Hq9MkKm9yPqDkasp8eiyJbiPBxZ57eDCiBpA9ZegUAzN4W45OQSduhe3kRf3c1dccD3JoLZalo84eIhspz3iSQ_lE_xJ3UYdzBAQhRPubeBV3vVWj1JRNMjFLBiy.FcMSL0apJtGL8puPZyYlhFzfiPCQRuTsg0gLlOYXxdTHt7RK.VrGx1urK.SVQGerd2sNZxT1WxQhGotqhD1 Received: from [202.93.37.91] by web76214.mail.sg1.yahoo.com via HTTP; Sat, 29 Aug 2009 15:02:21 PDT X-Mailer: YahooMailClassic/6.1.2 YahooMailWebService/0.7.338.2 Date: Sat, 29 Aug 2009 15:02:21 -0700 (PDT) From: Yogi Rizkiadi Subject: NEED HELP COPY TO DYNAMIC OUTPUT FILE To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-965440482-1251583341=:88507" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=4.18 tagged_above=-10 required=5 tests=BAYES_50=0.001, DNS_FROM_RFC_BOGUSMX=1.482, HTML_MESSAGE=0.001, RCVD_IN_SORBS_WEB=0.619, SUBJ_ALL_CAPS=2.077 X-Spam-Level: **** X-Archive-Number: 200908/150 X-Sequence-Number: 33059 --0-965440482-1251583341=:88507 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable 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:=3D0; j:=3D10; WHILE i < j LOOP COPY (SELECT * FROM country) TO '/usr/proj/' || i || '.txt'; // nee= d 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 =0A=0A=0A Coba Yahoo! Messenger 9.0 baru. Lengkap dengan segala yang A= nda sukai tentang Messenger! http://id.messenger.yahoo.com --0-965440482-1251583341=:88507 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Hi admin, i'm gie from indonesia

i wan= na ask you how to make a dynamic output file from command COPY TO ?

= i have tried this :

BEGIN
i:=3D0;
j:=3D10;
WHIL= E i < j LOOP
COPY (SELECT * FROM country) TO Dapatkan alamat Email baru Anda!
=0ADapatka= n nama yang selalu Anda inginkan sebelum diambil orang lain! --0-965440482-1251583341=:88507-- From pavel.stehule@gmail.com Sun Aug 30 06:52:08 2009 Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id AE94663267A for ; Sun, 30 Aug 2009 03:52:08 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 38331-05 for ; Sun, 30 Aug 2009 06:51:55 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gx0-f219.google.com (mail-gx0-f219.google.com [209.85.217.219]) by mail.postgresql.org (Postfix) with ESMTP id 079DC6323E8 for ; Sun, 30 Aug 2009 03:51:57 -0300 (ADT) Received: by gxk19 with SMTP id 19so4048011gxk.0 for ; Sat, 29 Aug 2009 23:51:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=jpavqzNbxQUSZ8Y67TAvjnZjre2X+crSvQIkWwjAuIY=; b=NfIj9clNsGqn+QJ4YI5AIulOA+M0QaU0wXUFAYtbKraUJuvsMedRbE9ah2l8BrBQzr UcbEytkCjq9BFCOhtbKd1+Onjia/vQOVmsAiufXU9R0R6MVPIX24fEx6sDBo/OY2nbIl y3R7DQ+mfCb3I1mDsNSaBFHm2Q+5Ebw1NvYI8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=qssw2TYnHr86Ibuphyg2bnoLam+dkwsNreIOz/GKG+PU35sSW0yOVWJWCuLOD4SQxS eOkDEK3ehVfcJvgKCcHTUZIA1UObKGpdLWPhO3FMqd2JafsjQxkvR2s0Zys4278w8OCj kM0+c8BGS0FqlibGLdfPFAz2j2lSMIl4yVL7o= MIME-Version: 1.0 Received: by 10.150.65.21 with SMTP id n21mr5718762yba.89.1251615116857; Sat, 29 Aug 2009 23:51:56 -0700 (PDT) In-Reply-To: <797097.88507.qm@web76214.mail.sg1.yahoo.com> References: <797097.88507.qm@web76214.mail.sg1.yahoo.com> Date: Sun, 30 Aug 2009 08:51:56 +0200 Message-ID: <162867790908292351g14087219r970576b9006fb91b@mail.gmail.com> Subject: Re: NEED HELP COPY TO DYNAMIC OUTPUT FILE From: Pavel Stehule To: Yogi Rizkiadi Cc: pgsql-sql@postgresql.org Content-Type: text/plain; charset=UTF-8 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.8 tagged_above=-10 required=5 tests=AWL=-0.690, BAYES_05=-1.11 X-Spam-Level: X-Archive-Number: 200908/151 X-Sequence-Number: 33060 Hello COPY in plpgsql are not allowed. regards Pavel Stehule 2009/8/30 Yogi Rizkiadi : > 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! From tgl@sss.pgh.pa.us Sun Aug 30 15:54:07 2009 Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 3275C633302 for ; Sun, 30 Aug 2009 12:54:07 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 26760-10 for ; Sun, 30 Aug 2009 15:53:56 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 78CCC632CD1 for ; Sun, 30 Aug 2009 12:53:56 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n7UFrseK013183; Sun, 30 Aug 2009 11:53:54 -0400 (EDT) To: Pavel Stehule cc: Yogi Rizkiadi , pgsql-sql@postgresql.org Subject: Re: NEED HELP COPY TO DYNAMIC OUTPUT FILE In-reply-to: <162867790908292351g14087219r970576b9006fb91b@mail.gmail.com> References: <797097.88507.qm@web76214.mail.sg1.yahoo.com> <162867790908292351g14087219r970576b9006fb91b@mail.gmail.com> Comments: In-reply-to Pavel Stehule message dated "Sun, 30 Aug 2009 08:51:56 +0200" Date: Sun, 30 Aug 2009 11:53:54 -0400 Message-ID: <13182.1251647634@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.596 tagged_above=-10 required=5 tests=AWL=0.003, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200908/152 X-Sequence-Number: 33061 Pavel Stehule writes: > COPY in plpgsql are not allowed. I think it will work if you use an EXECUTE. regards, tom lane From pavel.stehule@gmail.com Sun Aug 30 15:56:41 2009 Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id BBA68633FE7 for ; Sun, 30 Aug 2009 12:56:41 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 27256-07 for ; Sun, 30 Aug 2009 15:56:30 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yw0-f186.google.com (mail-yw0-f186.google.com [209.85.211.186]) by mail.postgresql.org (Postfix) with ESMTP id 03031633FD5 for ; Sun, 30 Aug 2009 12:56:30 -0300 (ADT) Received: by ywh16 with SMTP id 16so3482611ywh.19 for ; Sun, 30 Aug 2009 08:56:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=ru8FHhiCj1qCOGIsDg8A+HYZ2JhLj4SIwt0OMnbJ4xM=; b=WEHoED0jdBCjMWAPQtL0/gbMphhY2zbAXliRKI3O3914zqRc6xVlsPBnvLO7chuXr8 L+8Cc7OuEPKigKTz/10XESFGrT11YHoQ27erf+orFhystNCGi34UnLlBKWp9EDncuR92 Keu9A9ewhKJDEj8Ds+xOg8yWjq7X4P8ORSLBM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=C+HsMiSKilPi7qkJLT7FnL0RWzOqLIr+XPiG4fT0qYKJNorD9rDJE5QgpWYZVaObeR D3NlnXf5n+7smbls3LTVbugpqgc032HMKD88Udo7+J/6BBxgjSnmykJJcobN9ALkNVBv /B/4GBtMpzr4LGV932wbTeYtgER0yf18I+plQ= MIME-Version: 1.0 Received: by 10.150.26.5 with SMTP id 5mr6935069ybz.228.1251647789785; Sun, 30 Aug 2009 08:56:29 -0700 (PDT) In-Reply-To: <13182.1251647634@sss.pgh.pa.us> References: <797097.88507.qm@web76214.mail.sg1.yahoo.com> <162867790908292351g14087219r970576b9006fb91b@mail.gmail.com> <13182.1251647634@sss.pgh.pa.us> Date: Sun, 30 Aug 2009 17:56:29 +0200 Message-ID: <162867790908300856u1eb20f66sfeec34a52ab6db03@mail.gmail.com> Subject: Re: NEED HELP COPY TO DYNAMIC OUTPUT FILE From: Pavel Stehule To: Tom Lane Cc: Yogi Rizkiadi , pgsql-sql@postgresql.org Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.526 tagged_above=-10 required=5 tests=AWL=0.073, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200908/153 X-Sequence-Number: 33062 2009/8/30 Tom Lane : > Pavel Stehule writes: >> COPY in plpgsql are not allowed. > > I think it will work if you use an EXECUTE. > > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0regards, tom lane > I didn't test it. regards Pavel Stehule From gie05tech@yahoo.co.id Mon Aug 31 09:30:40 2009 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 22C3F63514B for ; Mon, 31 Aug 2009 06:30:40 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 31660-08-3 for ; Mon, 31 Aug 2009 09:30:32 +0000 (UTC) Received: from mx3.hub.org (mx3.hub.org [206.223.169.73]) by mail.postgresql.org (Postfix) with ESMTP id 12872633DFB for ; Mon, 31 Aug 2009 06:30:15 -0300 (ADT) X-Greylist: delayed 00:06:37.377523 by SQLgrey-1.7.6 Received: from smtp126.mail.sg1.yahoo.com (smtp126.mail.sg1.yahoo.com [124.108.123.197]) by mx3.hub.org (Postfix) with SMTP id 0F5A537C88E for ; Mon, 31 Aug 2009 02:55:27 -0300 (ADT) Received: (qmail 16575 invoked from network); 31 Aug 2009 05:47:44 -0000 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= ; Received: from unknown (HELO VIRUS) (gie05tech@202.153.230.141 with login) by smtp126.mail.sg1.yahoo.com with SMTP; 31 Aug 2009 05:47:43 -0000 X-Yahoo-SMTP: AYpYPYuswBAfuJ6zBDCZXY0RgJPV_A-- X-YMail-OSG: iHiJfzcVM1kFTLIgXv8GP20QCWZ678IdgjV8JSoXFgm4dkAPjVlx1Wpfjp7bEUAdzIsMZpoAibfQ_6DAmc6EkT7_zpKLWxUFPdqzikduyY.temLCb7wEv7hg4mBDr4_mh21jRWHLGx40x8UPtQEei7y4fLrXNwURZLXVBhZIxi.QFMKjT5bGYVlKJLbR5UIfrWNA5vKZj16GMI2VKDOUPjcZ5EBThb3imUfQx3OPmQ8JodPl67RVJQ-- X-Yahoo-Newman-Property: ymail-3 From: "Yogi Dwianandono Rizkiadi" To: "'Pavel Stehule'" , "'Tom Lane'" Cc: 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 X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AcopinnZ1l1+dlPqQyKx+dPVIteujQAchpow Content-Language: id X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.356 tagged_above=-10 required=5 tests=BAYES_00=-2.599, DNS_FROM_RFC_BOGUSMX=1.482, MIME_QP_LONG_LINE=1.396, SUBJ_ALL_CAPS=2.077 X-Spam-Level: ** X-Archive-Number: 200908/155 X-Sequence-Number: 33064 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 : > Pavel Stehule 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