Received: from smtp014.mail.yahoo.com (smtp014.mail.yahoo.com [216.136.173.58]) by postgresql.org (8.11.3/8.11.1) with SMTP id f6I02Ga49940 for ; Tue, 17 Jul 2001 20:02:16 -0400 (EDT) (envelope-from chrup999@yahoo.com) Received: from chill-025.bbnow.net (HELO ?192.168.0.4?) (24.219.2.25) by smtp.mail.vip.sc5.yahoo.com with SMTP; 18 Jul 2001 00:02:12 -0000 X-Apparently-From: User-Agent: Microsoft-Entourage/9.0.1.3108 Date: Tue, 17 Jul 2001 19:01:57 -0500 Subject: Re: PL/PGSQL and external (flat ASCII) files - Urgent ... :) From: Chris Ruprecht To: "pgsql-sql@postgresql.org" Message-ID: In-Reply-To: Mime-version: 1.0 X-Priority: 1 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit X-Archive-Number: 200107/239 X-Sequence-Number: 4035 Hi Josh, on 07/17/2001 18:31, Josh Berkus at josh@agliodbs.com wrote: > Chris, > > Hmmph. People are in a bad mood this week; obviously few other people > on the list have had to write applications for the banking industry, > which trades everything in flat files. Give Chris a break! > Thanks ;). I'm new to PostGreSQL and to PHP and to SQL - I have done Progress RDBMS/4GL for the last 12 years and it absolutely spoils you. But in the last 2 weeks, I have hammered out an application (you can have a look at http://chill-025.bbnow.net, use user demo and password demo). >>> From an input file where the records looks like this one: >> >> 020-13 016-05 07-15-2001 23:59:07 00:00:59 09678634321208 >> 78634321208 >> 0000005300 ^M > > The answer to your question is somewhat annoying, though: You can't use > PL/pgSQL for this task. Basically, two other PostgreSQL function > languages - PL/tcl and PL/perl - have excellent text-parsing ability. > As such, there is no movement affort to replicate that functionality in > PL/pgSQL. Well, I found a way. I created this table: Attribute | Type | Modifier -----------+------------------------+---------- s | character varying(400) | type | character varying(10) | Index: i_dl2_type To which I copy (psql copy statement) two files. One contains s = entity code and type = 'entity', the others has a long string of data and NULL in the type. I found that pl/pgsql has the functions I need (btrim, substring, mod, date stuff) which are virtually identical to the statements in the Progress-4GL code. This allows me to get my data into the database pretty efficiently. Sorry, Tom, I can not use a text muncher, since I have to populate, for example the Pseq field with data, which comes out of another table to link these records together (a phone call is linked to an area code, which in turn is linked to a country, which is linked to a rate at which the call is charged, etc.) I can not go the perl or TCL route since I do this all in my spare time. A friend of mine needs this application for his business and we have plans to sell it on, once we have it completed. So, the only hacker who plays with this right now, is me, and me has no clue about Perl or TCL and although me would like to learn these languages one day, this applications need to be completed no later than this coming weekend. The pl/pgsql part is just a small portion of the whole thing. I still need to figure out a way to export data which I calculate to let gnuplot create pretty pictures for me. And today is the first day, I try pl/pgsql ... Today, the data import program must get finished ... Do I need to say more? Best regards, Chris _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com