Thread: PL/PGSQL and external (flat ASCII) files - Urgent ... :)
Hi all, I need to know how I can access a flat file from within a PL/PGSQL script. I have an Oracle PL/SQL book here and they refer to FOPEN(...), FCLOSE(...) but these functions don't seem to exist in PL/PGSQL.. What can I do instead? I checked the Programmer's Guide to Procedural Languages (PostGreSQL 7.1.2 and 7.2) but there is no info on it. Best regards, Chris _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
The hack and slash Perl programmer in me says--- if you only plan to do this once, (like importing data), then just write a Perl script that *generates* SQL code that does your inserts, updates, or whatever. You can then execute thsi with psql -f filename. But if you're trying to use flat files programmtically...(aren't you using a database to avoid flatfiles altogether? :-) hope that helps, robby -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Chris Ruprecht Sent: Tuesday, July 17, 2001 3:29 PM To: p-sql Subject: [SQL] PL/PGSQL and external (flat ASCII) files - Urgent ... :) Importance: High Hi all, I need to know how I can access a flat file from within a PL/PGSQL script. I have an Oracle PL/SQL book here and they refer to FOPEN(...), FCLOSE(...) but these functions don't seem to exist in PL/PGSQL.. What can I do instead? I checked the Programmer's Guide to Procedural Languages (PostGreSQL 7.1.2 and 7.2) but there is no info on it. Best regards, Chris _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
You can probably try to make a c extension that does open/close. On 17 Jul 2001 15:28:36 -0500, Chris Ruprecht wrote: > Hi all, > > I need to know how I can access a flat file from within a PL/PGSQL script. > I have an Oracle PL/SQL book here and they refer to FOPEN(...), FCLOSE(...) > but these functions don't seem to exist in PL/PGSQL.. What can I do instead? > I checked the Programmer's Guide to Procedural Languages (PostGreSQL 7.1.2 > and 7.2) but there is no info on it. > > Best regards, > Chris > > > > _________________________________________________________ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > -- Wei Weng Network Software Engineer KenCast Inc.
"Chris Ruprecht" <chrup999@yahoo.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
Chris Ruprecht writes: > I need to know how I can access a flat file from within a PL/PGSQL script. You can't. PL/TclU could help you there. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
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@sss.pgh.pa.us wrote: > "Chris Ruprecht" <chrup999@yahoo.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
Chris Ruprecht <chrup999@yahoo.com> writes:
> 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:
> ...
> From an input file where the records looks like this one:
If it's just a data import issue, why do you want to do it in a plpgsql
function? Do the format massaging on the client side. I'd think about
making a simple little sed or perl script (or whatever text-masher you
like) producing data that COPY would take.
regards, tom lane
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! > >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. So: Hire yourself a perl or tcl hacker. Have them write parsing functions in pl/tclU or pl/perl to load the records. Then have your PL/pgSQL function call the tcl or perl functions. You'll need to get advice from other list members or the docs (and don't forget the "non-FAQ Documentation" page!) on how to use these other languages, as I have had no need for them, to date. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
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
Crhis, > 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). Hey, if you're fond of 4GL, somebody's grafted 4GL as a trusted language for PostgreSQL. I'll send the web page when I can find it ... > 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. Glad you found something that works. > 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? So ... this friend saved your life recently? -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco