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

Hi Tom,

If life was easy, everything would just come in the format I'd like it. But
since it isn't, I need to create records like this one:

cdate | ctime | countrycode | areacodex | success | carrier |
duration| phonenumber | areacode | pseq | loadno | frline | entity |
loaddate | loadtime | prefix | toline | dur306 | dur180180 | recno
------------+----------+-------------+-----------+---------+---------+------
----+-------------+----------+------+---------+--------+--------+-----------
-+----------+--------+--------+--------+-----------+---------
2001-07-15 | 23:55:52 | 98 | 0 | f | 53092 |
48| 11970 | | 0 | 8280646 | 2017 | 001 | 2001-07-16 |
02:05:48 | 092 | 2116 | 0.8 | 3 | 5493891

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

(yes, that is a crtl-M at the end of the record, believe it or not :). If
there is any other way of getting this data into the database, I would love
to know about it. I have written a pretty complex routine to scan the phone
number in the input record to figure out what country is called and then,
within the country, which area was called. Country codes can be 1, 2 or 3
digits long.

As you can see, this is not a simple 'import' where I simply use 'copy'.
What it looks like I will do is to import this into a temp record, which I
then cut to pieces, extracting the info I want. Once I have what I need to
create the actual record, I can delete the temp record.

Best regards,
Chris

on 07/17/2001 16:06, Tom Lane at tgl(at)sss(dot)pgh(dot)pa(dot)us wrote:

> "Chris Ruprecht" <chrup999(at)yahoo(dot)com> writes:
>> I need to know how I can access a flat file from within a PL/PGSQL script.
>
> You can't --- and you should ask yourself hard questions about why your
> system design needs that, or whether the data in question shouldn't be
> inside the database to begin with. Among other problems, data in a flat
> file will not be under transactional control. That could cause such
> interesting problems as a transaction that logically precedes another
> one being able to see a later version of the flat file than the "later"
> transaction sees.
>
> If you are absolutely intent on doing this, you could use a function
> coded in C or in pltclu (unsecured pltcl). But there's no feature in
> plpgsql to do it, and requests for one are not likely to meet with much
> favor.
>
> regards, tom lane

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com