Re: PL/PGSQL and external (flat ASCII) files - Urgent ... :)

Hi Josh,

on 07/17/2001 18:31, Josh Berkus at josh(at)agliodbs(dot)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