Re: [SQL] ERROR: UNIQUE constraint matching given keys for referenced table "sequences" not found
From: "Dado Feigenblatt" <[EMAIL PROTECTED]> > I'm trying to create some tables with foreign keys. > When I try to create a foreign key ... > > foreign key(seq_code) references sequences(seq_code) on update > CASCADE on delete CASCADE, > > I get this message: > > ERROR: UNIQUE constraint matching given keys for referenced table > "sequences" not found > > The problem is that the referenced field and table exist. > Any hint? Do you have a unique index on sequences.seq_code? - Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [SQL] Re: drop table if exists
Alright, but what about when you're writing a long dump file with all these sequences, functions not speaking about the tables themselves. When I drop something I wanna make sure it out the database (but still cannot empty the whole database before), so I have DROP TABLE ..., ..., ..., ...; DROP FUNCTION ..., ..., ..., ...; DROP SEQUENCE ..., ..., ..., ...; If one of these were not there, the whole dump crashes and I need to restart the process modifying the SQL file. I'd LOVE to have a magic 'IF EXISTS' like in mySQL - make life easier (Although I totally disrespect mySQL). Has anyone a work around for this? Thanks, and yes, sorry for the late post, just looked you up guys. Sincerely, Maxim Maletsky Founder, Chief Developer PHPBeginner.com (Where PHP Begins) [EMAIL PROTECTED] www.phpbeginner.com -Original Message- From: Philip Hallstrom [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 04, 2001 7:39 AM To: Jason Watkins Cc: [EMAIL PROTECTED] Subject: [SQL] Re: drop table if exists Just drop the table using "DROP TABLE mytable;" and ignore the error... I'm sure there are fancy ways of doing it by accessing system tables, but the above works for me. On Tue, 3 Jul 2001, Jason Watkins wrote: > How can I duplicate the behavior of: > > DROP TABLE IF EXISTS mytable; > > CREATE TABLE mytable ( > blah, > blah > ); > > INSERT INTO mytable > (blah) > VALUES > (blah); > > in other words, so that I have a single sql file that restores the database > to a known state. > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Performance tuning in PostgreSQL?
Em 10 Jul 2001, Raymond Chui escreveu: >In other database systems, such as Informix, Sybase, etc. The database >is stored >in the raw disk partitions, you can config the database system into >different >partitions, different disks, slice into different trunks, etc. But >PostgreSQL is >stored the database in the file system in PGDATA directory. ...and how to use two processors in a query (if it's possible)? We have a new server with two CPU and, in a query, Postgres uses only one of them. Thanks, Edipo Elder [[EMAIL PROTECTED]] _ Oi! Você quer um iG-mail gratuito? Então clique aqui: http://registro.ig.com.br/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Select distinct and order by.
Hi all: We have developed an aplication in C with postgresql, using libpq. Our version of development is Postgresql 6.5.3 and it works fine. Now we are migrating the application to various flavours of Linux (S.u.s.e 7.1, tha uses postgresql 7.0.3; Debian Potato, that uses postgresql 7.0.2) and in these dists the next query fails. Select distinct field1, field2 from table1 order by field3; The value return by PQresultErrorMessage is: For SELECT DISTINCT, ORDER BY expressions must appear in target list Whatever this query works fine in postgresql 6.5.3. Is correct this query and so there was a bug on 6.5.3 or there is a bug on the new versions?. Also in certains situations (in versions 7.0.x) this query fails from libpq: Select distinct field1, field2, field3 from table1 order by field1, field2; but if we copy the statement with mouse and do: $psql -d ourdb -c "Select distinct field1, field2, field3 from table1 order by field1, field2;" WORKS FINE. These last situation are in a transaction whith some tables locked, that aren't table1. TIA Carlos. Solaria Mediterranea, S.L.L: P.S.: please send me the answers directly, because I isn't subscribed to the list. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] How Postgresql Compares For Some Query Types
Dear list, With the advent of Version 7.1.2 I thought it would be interesting to compare how Postgresql does a certain class of queries (Star Queries), and Data Loads with some of the other leading databases ( which were in my humble opinion Mysql 3.23.39, Db2 7.2 ,Oracle 9.0 [ ok - Possibly SQL 2000 too but I dont run Winanyk] ). The results were overall very encouraging : Postgresql can clearly hold its own when compared to the "big guys". The full details (together with a wee rant) are aviailable on : http://homepages.slingshot.co.nz/~markir (if anyone asks I can submit the entire results...but I figured, lets cut to the chase here) There were two areas where Postgresql was slower, and I thought it would be informative to discuss these briefly : 1 Star query scanning a sigificant portion of a fact table SELECT d0.f1, count(f.val) FROM dim0 d0, fact1 f WHERE d0.d0key = f.d0key AND d0.f1 between '2007-07-01' AND '2018-07-01' GROUP BY d0.f1 This query requires summarizing a significant proportion of the 300 row ( 700Mb ) fact1 table. Postgres 7.1.2 executed this query like : Aggregate (cost=2732703.88..2738731.49 rows=120552 width=20) -> Group (cost=2732703.88..2735717.69 rows=1205521 width=20) -> Sort (cost=2732703.88..2732703.88 rows=1205521 width=20) -> Hash Join (cost=1967.52..2550188.93 rows=1205521 width=20) -> Seq Scan on fact1 f (cost=0.00..1256604.00 rows=300 width=8) -> Hash (cost=1957.47..1957.47 rows=4018 width=12) -> Index Scan using dim0_q1 on dim0 d0 (cost=0.00..1957.47 rows=4018 width=12) for an elapsed time of 3m50s Wheras Oracle 9.0 used : SELECT STATEMENT Optimizer=CHOOSE (Cost=5810 Card=4020 Bytes =60300) SORT (GROUP BY) (Cost=5810 Card=4020 Bytes=60300) HASH JOIN (Cost=5810 Card=2989644 Bytes=44844660) TABLE ACCESS (FULL) OF 'DIM0' (Cost=4 Card=4020 Bytes= 40200) TABLE ACCESS (FULL) OF 'FACT1' (Cost=5806 Card=2990089 Bytes=14950445) for an elapsed time of 50s. It would seem that Oracle's execution plan is more optimal. 2 Bulk loading data Buld Load times for a 300 row (700Mb ) fact table were Postgresql 9m30s (copy) Db2 2m15s (load) Oracle 5m (sqlldr) Mysql 2m20s (load) (Note that Db2 is faster than Mysql here ! ) While I left "fsync = on" for this test... I still think the difference was worth noting. Any comments on these sort of results would be welcome. regards Mark ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Select distinct and order by.
On Wed, 11 Jul 2001, Carlos wrote: > Select distinct field1, field2 from table1 order by field3; > > The value return by PQresultErrorMessage is: > > For SELECT DISTINCT, ORDER BY expressions must appear in target list That's because this query is ambiguous. What if table1 looks like this: field1 field2 field3 -- -- -- a b 1 a b 3 c d 2 What should your query return then? Both a b c d and c d a b are valid, depending on which 'a b' row was chosen. If 6.5.3 allowed that, it was a mistake to rely on it because the answer is undefined. -- Tod McQuillin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] First steps in plpgsql - language not recognized?
Hi all, I have tried (and failed) my first steps in Pl/PgSQL. This must be real simple (like a 'create language' or something) but I don't (yet) know how to do this and maybe someone has a quick answer for me here. I get this (I'm sure it's a clear case of RT(F)M, but there are so many Ms to R, I don't know where to begin): [postgres@chill-025 postgres]$ psql -f x.sql phones psql:x.sql:1: ERROR: RemoveFunction: function 'testfunc(int4)' does not exist psql:x.sql:9: ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal, and created procedural languages. The first error is ok - the error in line 9 is what I'm worried about. The code is what's in the tutorial, no biggy ;). Best regards and thanks for the help, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] using custom column names in a group by clause
The following select sets two custom column names using the same
original column name. When I try to include a custom column name in a
group by clause I get ORA-00904: invalid column name from Oracle. How
can I access a column name I created in the group by clause?
Here's the SQL: DISP is the column causing the error
select userid,matlid,wsid,
substr(note,instr(note,'Status') + 8,(instr(note,',',1,3) -
(instr(note,'Status') + 8))) as DISP,
sum(substr(note,instr(note,'Qty') + 5,(instr(note,',',1,1) -
(instr(note,'Qty') + 5 as QTY,
action
from ise_prodordlog
where userid = '4500925'
and action in ('CompleteItem','UndoCompleteItem')
and actiontime >= to_date('06/20/2001 00:00:00','MM/DD/
HH24:MI:SS')
and actiontime < to_date('06/30/2001 00:00:00','MM/DD/
HH24:MI:SS')
group by userid,matlid,wsid,DISP,action
order by userid,matlid,wsid,DISP,action;
Matt Rinkenberger
Avaya, Inc.
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] No subselects in constraint (bug?)
Hello there! [Please Cc: me in followups.] I tried the following: CREATE TABLE a ( int4 id ); CREATE TABLE b ( int4 id CHECK (id = ANY(SELECT a.id FROM a)) ); Tables are created ok, checking with '\d table' confirms it. But when I try to insert into table b, e.g.: INSERT INTO b (id) VALUES (0); I get: ERROR: ExecEvalExpr: unknown expression type 108 Of course, the tuple is not inserted. As quick dig of code showed, type 108 is T_SubLink which is created for ANY() subselect, and ExecEvalExpr() function does not handle this type of node. Is it intentional or a bug? I use 7.0.3, but 7.1.2 code looks pretty the same in ExecEvalExpr(). The platform is FreeBSD 4.3-R. TIA for responses, Alexey. -- -+--- Yes. We have good news!| Regards, Alexey V. Neyman Well, that is to say, we have no news. | mailto: [EMAIL PROTECTED] --( Pkunk, SC2 )-+--- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] CAST(ipaddress as text) -- HOW?
hi all, how can we cast an ipaddress(type cidr) to any other type? thankx in advance.. Regards, Bhuvaneswar. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How Postgresql Compares For Some Query Types
Mark, (and Tom below) Thanks for doing the tests. These'll be useful to me if any client asks me again if PostgreSQL is a "real" database. I'm particularly intrigued that MySQL performed so poorly. In the field, it's been quite speedy but maybe that's because applications using it are restricted to simple queries. You may wish to post your link to Slashdot and pgsql-announce as well. I'm sure the Great Bridge folks will be interested. I'm a little unclear on what a "fact table" is. Can you explain? Finally, Tom, I figured out what was up with the 30-45 second response time on the large star query I couldn't get to perform. Turns out that the client had changed two of the VARCHAR fields to TEXT fields and added large blocks of text (> 16k for some) to them. When I removed these fields from the query, it gives me the <7s response time I'd expected. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] First steps in plpgsql - language not recognized?
man createlang -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [SQL] First steps in plpgsql - language not recognized?
Chris, sounds like you haven't called 'createlang plpgsql database-name' at the comand prompt. Try executing this and see if it works. -Robby -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Chris Ruprecht Sent: Tuesday, July 17, 2001 8:49 AM To: p-sql Subject: [SQL] First steps in plpgsql - language not recognized? Hi all, I have tried (and failed) my first steps in Pl/PgSQL. This must be real simple (like a 'create language' or something) but I don't (yet) know how to do this and maybe someone has a quick answer for me here. I get this (I'm sure it's a clear case of RT(F)M, but there are so many Ms to R, I don't know where to begin): [postgres@chill-025 postgres]$ psql -f x.sql phones psql:x.sql:1: ERROR: RemoveFunction: function 'testfunc(int4)' does not exist psql:x.sql:9: ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal, and created procedural languages. The first error is ok - the error in line 9 is what I'm worried about. The code is what's in the tutorial, no biggy ;). Best regards and thanks for the help, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Select distinct and order by.
Carlos <[EMAIL PROTECTED]> writes: > Select distinct field1, field2 from table1 order by field3; > The value return by PQresultErrorMessage is: > For SELECT DISTINCT, ORDER BY expressions must appear in target list > Whatever this query works fine in postgresql 6.5.3. > Is correct this query and so there was a bug on 6.5.3 or there is a bug > on the new versions?. 6.5 was in error to accept that query. The problem with it is: which value of field3 should be used to sort, if multiple rows with the same field1/field2 are being collapsed together? The results aren't well-defined. You can probably accomplish what you want in a slightly better-defined way with SELECT DISTINCT ON. See the SELECT reference page. > Also in certains situations (in versions 7.0.x) this query fails from > libpq: "Fails" how? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] using custom column names in a group by clause
Matt, First of all, this is a PostgreSQL list, so we're not going to be able to give you much help with Oracle. > The following select sets two custom column names using the same > original column name. When I try to include a custom column name in > a > group by clause I get ORA-00904: invalid column name from Oracle. > How > can I access a column name I created in the group by clause? Second, I'd say that your answer is self-evident; use the real column name rather than the custom column name. If the column is just being aliased in order to display it twice, then it doesn't matter which one you group by. Third, Matt, PostgreSQL 7.1.2 DOES support aliasing in GROUP BY clauses. Maybe it's time to switch databases? select client_name as clname1, client_name as clname2, AVG(status) from clients group by clname1, clname2 -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Building RPMS with plperl (solution)
This is not the right mail list, but I wanted to get it into
google/archives (I will forget what I did in 15 minutes, and I could
find little documentation on this process).
To build the redhat 7.1 plperl rpm, you merely need to
rpm -Uvh postgresql-7.1.2-4PGDG.src.rpm
then there should be a command along the lines of
rpm -bb /usr/src/redhat/SPECS/postgresql.spec --define 'forceplperl 1'
But that failed.
So I hand changed
%{?forceplperl:%define plperl %{expand:forceplperl}}
%{!?forceplperl:%define forceplperl 0}
%{!?plperl:%define plperl 0}
to
%{!?pltcl:%define pltcl 1}
%{!?forceplperl:%define forceplperl 1}
%{!?plperl:%define plperl 1}
And it built.
Then I loaded it:
rpm -Uvh
/usr/src/redhat/RPMS/i386/postgresql-plperl-7.1.2-4PGDG.i386.rpm
Now to test it :)
--
Zot O'Connor
http://www.ZotConsulting.com
http://www.WhiteKnightHackers.com
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [SQL] PL/PGSQL and external (flat ASCII) files - Urgent ... :)
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] PL/PGSQL and external (flat ASCII) files - Urgent ... :)
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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] PL/PGSQL and external (flat ASCII) files - Urgent ... :)
"Chris Ruprecht" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] PL/PGSQL and external (flat ASCII) files - Urgent ... :)
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 [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] 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 005300 ^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 [EMAIL PROTECTED] wrote: > "Chris Ruprecht" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] PL/PGSQL and external (flat ASCII) files - Urgent ... :)
Chris Ruprecht <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] PL/PGSQL and external (flat ASCII) files - Urgent ... :)
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 > 005300 ^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 information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] PL/PGSQL and external (flat ASCII) files - Urgent ...:)
Hi Josh, on 07/17/2001 18:31, Josh Berkus at [EMAIL PROTECTED] 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 >> 005300 ^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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] PL/PGSQL and external (flat ASCII) files - Urgent ... :)
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 [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Re: [GENERAL] trigger on DELETE
On Wed, 11 Jul 2001, Stephan Szabo wrote: > On Wed, 11 Jul 2001, Phuong Ma wrote: > > > I'm trying to define a trigger that copies the row to be deleted into > > another table (which is the inventory_audit table) before it does the > > delete from the original table (which is the inventory table). > > > > CREATE FUNCTION inv_audit_mod () RETURNS OPAQUE AS ' > > BEGIN > > > > NEW.user_aud := current_user; > > NEW.mod_time := ''NOW''; > > > > INSERT INTO inventory_audit > > SELECT * FROM inventory WHERE id=NEW.id; > > > > RETURN NEW; > > END; > > ' LANGUAGE 'plpgsql'; > > > > CREATE TRIGGER inv_audit_mod BEFORE > > DELETE ON inventory > > FOR EACH ROW EXECUTE PROCEDURE inv_audit_mod(); > > > > Ok, the function works only with a trigger that is defined as ON INSERT > > OR UPDATE. If I try to define a trigger for ON DELETE and then delete a > > row from the table, there is nothing in the 'NEW' variable to return. I > > get an error message. If I define the function to return NULL, 0, or > > nothing, then it comes up with a type mis-match error. Is there anyone > > who can help? Thanks. > > I believe you want to use OLD rather than NEW for a delete trigger. Also you want to have your trigger fire AFTER DELETE and have it return NULL; cheers, thalis ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Why lost all foreign key constraits after cluster?
After I performed cluster indexname on tablename; All the foreign key constraints are lost on that table, why is that? I get messages like NOTICE: DROP TABLE implicitly drops referential integrity trigger from table "tablename" during cluster I don't want to lost the foreign key constraints on the table. How do I prevent that? Thank you! --Raymond begin:vcard n:Chui;Raymond tel;fax:(301)713-0963 tel;work:(301)713-0624 Ext. 168 x-mozilla-html:TRUE url:http://members.xoom.com/rchui/ org:NWS, NOAA version:2.1 email;internet:[EMAIL PROTECTED] title:SA, DBA note:ICQ #: 16722494 adr;quoted-printable:;;NOAA, NWS, Office of Hydrology, OH=0D=0A1325 East-West Highway, Room 8112;Silver Spring;MD;20910-3283;U.S.A. x-mozilla-cpt:;-6384 fn:Raymond Chui end:vcard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
