Re: [SQL] Holiday Calculations?
clayton cottingham wrote: Brett Schwarz wrote: I couldn't resist, so I went ahead and did all of them. I might be using these in a future app anyways. Again these are Tcl procs, but you may be able to transfer the logical to another language, if you want. if you need this in perl i think Data::Manip might hep: sorry , i noticed a typo it should be Date::Manip ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Holiday Calculations?
Brett Schwarz wrote: I couldn't resist, so I went ahead and did all of them. I might be using these in a future app anyways. Again these are Tcl procs, but you may be able to transfer the logical to another language, if you want. if you need this in perl i think Data::Manip might hep: Date_IsHoliday $name=Date_IsHoliday($date); This returns undef if $date is not a holiday, or a string containing the name of the holiday otherwise. An empty string is returned for an unnamed holiday. also there is a section on holidays in the config file where you can set up your own : 1/1 = New Year's Day third Monday in Feb = Presidents' Day fourth Thu in Nov = Thanksgiving hope this is useful! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] calling a shell script from pl/pgsql
Stephan Szabo wrote:
On Mon, 10 Sep 2001, Jeff Barrett wrote:
How can I call a shell script from within a pl/pgsql function that is called
as from a trigger. I do not want to interact with the script I just want it
to run. I do want the trigger to wait for the script it called to complete
before returning a value.
Any ideas would be greatly appreciated.
I don't think you can do that from within pl/pgsql. You'd probably need a
function in pl/tclu or c.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
if its typical perl you should be able to
use backticks
my $system=`date`;
$system should be set to the date
or if you dont need any data back as you say
then just
system('date');
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] Feature Requests for 7.2 or 7.3 ...
Chris Ruprecht wrote: Hi guys, There are a number of features, I would love to see implemented in a future release of PostGreSQL, more specific, into PL/PGSQL. Is there a central place somewhere, where we (the PostGreSQL user community, I guess that is), is able to see what is planned for the future and where we can send some of the ideas we (constantly) have? It's nice to have such an active community with developers who actually listen to what their users have to say (I have some bad experiences with other systems...:). I just looked at the gram.y file for PL/PGSQL, but I have no idea what is actually going on there. I have a colleague here, who did compiler building in the past - he might understand the stuff though - maybe I can get him interested in implementing the one or other thing. Keep up the excellence ... Chris there is in fact a todo list trudge trudge dig http://postgresql.wavefire.com/docs/todo.html _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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] plperl
Time Co-Ordinate Tue, 05 Jun 2001 19:45:55 -0400, The Organism labeled Tom Lane said: clayton cottingham [EMAIL PROTECTED] writes: how does one execute an sql statement from inside a plperl function? At the moment, one doesn't. This is one of a number of features that have to be finished before plperl can be classed as more than a proof-of-concept exercise. However, Mark Hollomon seems to have lost interest or time to work on it, and no one else has picked up the ball. Any volunteers out there? regards, tom lane just looked at the code i wish i could say yes but it looks way beyond my level heres hoping someone pick up the torch ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[SQL] list of returns types for functions
is there a list of all possible returns for all procedural language types? aka perl can return these types c can return these .. thanks! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] execute client application from PL/pgSql
Jack wrote: Is that possible to execute a client application from server site by PL/Pgsql, such as pg_dump? Because my client sites are running Windows OS, or is there any Windows version of all Client Applications come from PostGreSQL V7.1? Jack ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster afaik if you have tcl/tk on windows should you not be able to run the pgaccess? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Cannot build PL/Perl ...
did you get this error when you tried to :
createlang plperl template1?
me too!
then i did this
find /usr -name libperl.so
/usr/lib/perl5/5.6.0/i386-linux/CORE/libperl.so
and then added
that dir {/usr/lib/perl5/5.6.0/i386-linux/CORE/}
to the /etc/ld.so.conf
ldconfig
and vroom it all went to the good
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] any proper benchmark scripts?
Justin Clift wrote: Hi Clayton, Was it opening a new connection to the database every time, or did it open one connection each time and pump multiple queries through it? It would be a good things to develop your script and benchmark this both ways. Could become a useful tool for sizing both of these databases. Out of curiosity do you have access to other databases such as Interbase, Oracle, DB2, Informix, and so forth? The more it can connect to, the better people will be able to understand each one's strengths and weaknesses, in terms of Perl's DB access. Regards and best wishes, Justin Clift ok i looked over the script and the connections are opened before for loops and shuit down after for loops i dont have access to any other db's but another person on the modperl list said he would be try oracle check out that thread its been going since monday? perl.apache.org click through maillists and use mailarchive.com search for 'fast db access' thats the thread!! ---(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] any proper benchmark scripts?
hello on the modperl list a good thread was happening called 'fast db access' find attached scripts used to do this here are my results: [drfrog]$ perl fast_db.pl postgres 16 wallclock secs ( 0.05 usr + 0.00 sys = 0.05 CPU) @ 400.00/s (n=20) mysql 3 wallclock secs ( 0.07 usr + 0.00 sys = 0.07 CPU) @ 285.71/s (n=20) postgres 17 wallclock secs ( 0.06 usr + 0.00 sys = 0.06 CPU) @ 333.33/s (n=20) mysql 3 wallclock secs ( 0.01 usr + 0.01 sys = 0.02 CPU) @ 1000.00/s (n=20) correct me if im wrong but if fast_db.pl is working right first set is insert second set is select comp stats running stock rpms from mandrake 7.2 for both postgresql and mysql 3.23.23-beta of mysql and 7.02 of postgresql [drfrog@nomad desktop]$ uname -a Linux nomad.localdomain 2.2.18 #2 Tue Apr 17 22:55:04 PDT 2001 i686 unknown [drfrog]$ cat /proc/meminfo total:used:free: shared: buffers: cached: Mem: 257511424 170409984 87101440 24219648 96067584 44507136 Swap: 2549432320 254943232 MemTotal:251476 kB MemFree: 85060 kB MemShared:23652 kB Buffers: 93816 kB Cached: 43464 kB SwapTotal: 248968 kB SwapFree:248968 kB [drfrog]$ cat /proc/cpuinfo processor : 0 vendor_id : AuthenticAMD cpu family : 6 model : 3 model name : AMD Duron(tm) Processor stepping: 1 cpu MHz : 697.535 cache size : 64 KB fdiv_bug: no hlt_bug : no sep_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 1 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 sep mtrr pge mca cmov pat pse36 psn mmxext mmx fxsr 3dnowext 3dnow bogomips: 1392.64 i will recomp both the newest postgresql and mysql not using any optimizing techs at all i'll post the config scripts i use -- back in the day we didn't have no old school -dr. frog http://www.hyperbomb.com it sells itself fast_db.pl benchmark.sql ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] any proper benchmark scripts?
hello: im try to find out how mysql or postgresql perform i was wondering if there are any benchmarking scripts out there? if so please lemme see em try em etc!! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] is this proper sql?
hi one of the developers here at work say this should work insert into detail (det_id,det_mas_id,det_date,det_amt) values (0,0,now(),'0'), (1,1,now(),'1'); but when i try that in postgres like: insert into detail (det_id,det_mas_id,det_date,det_amt) values (0,0,datetime'now','0'), (1,1,datetime'now','1'); i get ERROR: parser: parse error at or near "," now i personally dont think this is real sql anyone? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Calling Java from psql (was Re: [SQL] requesting help)
Peter Mount wrote:
At 10:33 26/03/01 +0200, Mathijs Brands wrote:
Has anybody ever tried calling Java code from a pgsql trigger written
in C? Shouldn't this be possible using JNI?
This was discussed recently.
I'm not exactly a Java expert myself, but this is the way PHP allows
you to call Java code from your PHP scripts.
It may be something to look at when things quieten down a bit. I'm rushed
of my feet at the moment (busy at work, long hours etc) but once 7.1 is
finally out of the door, I may get the chance to start looking at JNI ( my
employers don't mind me spending some time working on postgres when I'm not
on other projects either which is nice ;-) )
PS: the JDBC list is the best place for java based emails - I rarely read
sql ;-)
Peter
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
another possible angle is to use perl and its new inline module
{not sure if java is fully supported yet...}
or its other perl-java binding
it should be avail a t cpan
---(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] Postgres XML
Najm Hashmi wrote: Hi all, I was just wondering if there is way or some sort of utility to incorporate XML in postgrres. Thanks in advance. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ive done some simple processing using tables and xml::xslt basically generating a xml page based on db info and then parsing to html using the xslt's ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Quick question MySQL -- PgSQL
"Brett W. McCoy" wrote: On Tue, 6 Mar 2001, Josh Berkus wrote: Just a quick question ... I need to do a regular transfer (daily + on demand) of data from a MySQL database to a PostgreSQL database and back again. Can anybody steer me towards a good script for this, or do I have to write my own in PHP? Don't think there is an actual migration script (I could be wrong, though), but a program using Perl DBI or JDBC would make the data access a bit easier -- if you wrote it generically enough, you could make the data transfer go both ways with just a command-line switch. -- Brett http://www.chapelperilous.net/~bmccoy/ Give all orders verbally. Never write anything down that might go into a "Pearl Harbor File". ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) there is one in contrib as well there is some through freshmeat.net ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Date question
Francis Solomon wrote: Hi Boulat, stasis=# select (now() + '1 year')::date; ?column? 2002-03-06 (1 row) Hope this helps Francis Hi, Im a little bit stuck here. Does anyone know how to get date in format '-MM-DD' of a date one year from now. So for example today is '2001-03-06' I need to get date 12 months from now which will be '2002-03-06' in todays case... In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that doesnt work in PG. Regards, Boulat Khakimov ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] i dunno about you but i like this syntax better than the old :: ones select date(now()+ '1 year'); ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Veering OT opinions please XQL versus XML-QL
just wondering which of these two formats seems best pros and cons of each i know that supposedly xql is simpler in style than XML-QL but XML-QL has some nice sql like syntax the perl modules seem to work nicer too
[SQL] [notion]: a possible language addition: XQL
Hi all: has anyone heard of XQL? XQL is xml sql i just was catching up on some email and noticed this nugget http://www.ibiblio.org/xql/ anyways there is a xml::xql perl module so perl could do it but the ability to spit it right out of pg would be neato
Re: [SQL] How to represent a tree-structure in a relational database
Frank Joerdens wrote: On Wed, Dec 13, 2000 at 11:04:13AM -0800, Josh Berkus wrote: Frank, Please look in the list archives. About 2 months ago this topic came up and was discussed extensively (including a creative solution by yours truly). Hm, neither my archives nor a search on the postgresql.org page turned up the thread you mention. Do you recall which list it was and what the title of the thread was? Thanks, Frank yes i recall!! i managed to implement something to that effect a lot was done using postgres and perl anyone need code fragements?
Re: [SQL] How to represent a tree-structure in a relational database
Mathijs Brands wrote: On Wed, Dec 13, 2000 at 04:49:51PM -0800, Josh Berkus allegedly wrote: Stuart, I don't think I'd be comfortable with having the node_level column in the table structure. First, because you can derive that value using a function, it's duplicate data. Second, if you decide to take an entire segment of your hierarchy and move it under another node (by changing the value of node_linkup/ParentCategoryID), you'll need to recalculate all of those node_level values. And all the node_level values underneath it. I can see that. I suppose it depends on the data you're storing. The project I was working on tracked grocery inventory for a delivery service, and thus each item had a fixed "level" in the heirarcy (Food Class, Food Type, Manufacturer, and Item) and thus while items might get reassigned *across* the heirarcy, they did not get re-assigned *up and down* the heirarcy. Indeed. If the structure 'rarely' changes, having to do an expensive update may be acceptable, if it increase the overall performance significantly. Also, I can't think of a way to represent the tree in pure SQL without having the level identifiers (and a fixed number of levels). Storing only the parent for a record doesn't require you to keep track of levels, since this information can be reconstructed by following the chain of parent id's until you reach the top of your tree. Storing the children for each record (like I'm doing) works exactly the same. Just follow the 'path' (for instance 'Automotive Transport/Cars') to find the category you're looking for. Cheers, Mathijs -- "A book is a fragile creature. It suffers the wear of time, it fears rodents, the elements, clumsy hands." Umberto Eco this is the way i implemented too! i used perl and modperl/apache to deploy so i did use a perl module to store the top level of categories so they where always avail to the starting page mostly it ended up looking like the bidders edge horizontal line type thing again if anyone needs the code lemme know!
Re: [SQL] how to execute a C program via trigger ?
On Mon, 4 Dec 2000 18:20:29 -0800 (PST), S.F. Lee said: Thank for your hint, but I have some questions : 1. Do I have to compile the C program into a shared object (*.so)? yes 2. Do I have to use SPI (SPI is too complicate to me)? My request is very simple. I have a program (my_c_program) that I can execute it under shell by typing (my_c_program). Basically my_c_program is not a function. im not sure anyone? I want to fire a trigger after I update the field (x) in table foo, and the trigger can run the my_c_program. Is there an easy way to execute a PROCESS via trigger? regards,S.F. Lee id say dont use C!! use pl/sql but if your trying to learn the prog of c functions youll have to get help elsewhere unfortunatley im not even newbie status on c!
Re: [SQL] Where Can I find JDBC driver.
Mateusz Mazur wrote: Hello. I wonder where I can find JDBC driver for Postgresql. I think it should be class (sorry I am newbie). I would be very greatfull for quick response. Mateusz. download the source tarball go into src/interfaces/jdbc and follow directions!1 bon appetite!!
Re: [PHP-DB] Re: [SQL] a script that queries database periodically
Roberto Mello wrote: Jason wrote: aolserver is a web/application server. PHP is a server-side scripting language. Why exactly *should* it have a job scheduler? Some (such as myself) might also ask why should a web server have a job scheduler, but that's a thread for a different list :) Because PHP is supposed to solve web development problems. And this is one of them. It's very useful. -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto heya, IMHO it depends on why you are querying the db if you need internet access data such as how fast its querying or what have you then yes php could be useful if you need reports generated and mailed to someone a crond shell script would seem more logical
[SQL] Re: is there a mysql to postgresql sql converter?
On Sat, 18 Nov 2000 01:13:23 +0200, Max Fonin said: Can give a link ? can do : http://freshmeat.net/projects/mysql2pgsql/?highlight=convert+sql i havent tried their's did try yours it was nice to have the transactions! On Fri, 17 Nov 2000 13:45:37 -0800 clayton cottingham [EMAIL PROTECTED] wrote: Max Fonin wrote: Hi. I'm writing MySQL-Postgres dump converter. E.g. it will convert only database dumps produced by mysqldump. It's almost ready, problems are ENUM and SET types. I have problems with types emulation. Maybe someone help me guys ? Anyway, some half-working version attached. Max Rudensky. Name: my2pg.pl my2pg.plType: Perl Program (application/x-perl) Encoding: base64 thats cool!! i thought i saw another converter in the last couple o months on freshmeat too
[SQL] Re: is there a mysql to postgresql sql converter?
Max Fonin wrote: Hi. I'm writing MySQL-Postgres dump converter. E.g. it will convert only database dumps produced by mysqldump. It's almost ready, problems are ENUM and SET types. I have problems with types emulation. Maybe someone help me guys ? Anyway, some half-working version attached. Max Rudensky. Name: my2pg.pl my2pg.plType: Perl Program (application/x-perl) Encoding: base64 thats cool!! i thought i saw another converter in the last couple o months on freshmeat too
Re: [SQL] psql question
Joe Conway wrote: On machines where I've installed PostgreSQL 7.0.2 from RPM, psql allows use of the up arrow key for history and the escape/tab key for command completion, but on my remote web host (webpipe.net) those keys don't work. What do I need to do to get these features working on my remote web host? I've seen reference to .psqlrc in the psql man file, but not much else (no syntax, etc). If you compiled from source, make sure you had the readline-devel package installed. Thanks for your responses, Peter Mathijs. Actually, since the issue is on a hosted system, I don't have access to recompile. I was hoping that maybe there would be something I could configure from my own (non-root and non-postgres) account. My question actaully took several days just to make it to the list, so in the meantime I found my own work-around -- I copied local copies of psql and libpq up to my account on the web host. Then I put my own account folder to the front of the PATH and added a LD_LIBRARY_PATH. Now when I run 'psql' it works just like on my own machine! Thanks again for the response. Joe you could use a version of pgsql on your own machine and use the host switch to connect to an extrenal db of course your db isp will have to allow your ip to connect, not an easy thing if your dial up!! this way you could recompile your own ver of psql hope that helps
[SQL] Re: MySQL - Postgres dump converter
On Tue, 21 Nov 2000 00:01:33 +0200, Max Fonin said: Hi. MySQL-Postgres dump converter is now available at http://ziet.zhitomir.ua/~fonin/code/my2pg.pl. Still beta and bugsome version but working, supports MySQL ENUMs, near the end are SET emulation. Please help me to test. Max Rudensky. i just did a dump of one of our work tables and it produced a lot of functions returned as opaque .. which is ok, except i cant find said shared object anywhere on my sys what is this part of?
Re: [SQL] How to call a shell command in rule
On Wed, 25 Oct 2000 10:44:48 -0700, Jie Liang said: Hi, I want send a e-mail when the rows of mytable reaches 100,000, how? one way is to make a function using perl and use say mail::sender as the module to send the info use the code snippet in perldoc Mail::Sender another would be to build a cron perl job that would run every so often and check out how many rows then send email -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
