Re: [SQL] Compiling pl/pgsql functions
On Thursday 19 February 2004 19:26, Josh Berkus wrote: > Rodrigo, > > > I insist in my question, is there a way to compile the plpgsql codes or > > something like that, or its better to think about writting this postgres > functions in C?? > > No, there is not. Nor is there likely to be for any PL, as it would add > significant overhead for no real gain. It's worse than that - if you really denied access to them, you wouldn't be able to dump/restore the database - absolute nightmare. > Personally, I find it > hard to believe that any of my PL/SQL functions (or yours) are so brilliant > that they need trade secret protection. Some of mine are so ugly, I wish they were hidden away mind you ;-) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Row counts/data changes. Any catalog table that has this info?
Folks, Is there a catalog table or location where I can go to find data counts for tables? It would be nice if I could do a query which returned something like: table_name#Rows cust 1000 order 5000 order_detail 9500 without having to have the overhead of querying each table in turn. If there is such a source then is it accurate as of now or last vacuum or ??? On a related note...any location where I can see the most recent date a table was changed (insert/update/delete). Tx in advance... D --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.574 / Virus Database: 364 - Release Date: 1/29/2004 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] date format in 7.4
Silke Trissl <[EMAIL PROTECTED]> writes: > On 7.3 I run several tests about the format of the date and found, > that Postgres accepts almost everything. Today I found out, that 7.4.1 > only accepts dates in the format mm-dd-yy, It now requires the field order to be what DateStyle says it is. See the very voluminous flamewar about ambiguous date input handling in the pgsql-hackers archives from last summer. > although the documentation still states the following AFAICS the documentation says so too... regards, tom lane ---(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] date format in 7.4
On Fri, 20 Feb 2004, Silke Trissl wrote: > Hi, > > I have an application where users can enter the date via a web interface. > > Recently I upgrated my PostgreSQL version from 7.3 to 7.4.1. > > On 7.3 I run several tests about the format of the date and found, > that Postgres accepts almost everything. Today I found out, that 7.4.1 > only accepts dates in the format mm-dd-yy, although the documentation > still states the following > # > > 5. > >Otherwise the date field ordering is assumed to follow the > DateStyle setting: mm-dd-yy, dd-mm-yy, or yy-mm-dd. Throw an error if a > month or day field is found to be out of range. > > # > ref: http://www.postgresql.org/docs/7.4/interactive/datetime-appendix.html > > Does anyone know, if it is intentional to restrict it to mm-dd-yy format > or is is just a bug of 7.4.1? It is intentional. But, it's not as restricted as you might think. You can set the style to one of several ways you can set it. http://www.postgresql.org/docs/7.4/static/datatype-datetime.html the idea behind fixing this in 7.4 was that if you set a date style of SQL, DMY then it would be wring for the database to access a date of 3/14/04 and convert it to March 14th, since your date style said that march 14th should come in as 14/03/04. I.e. it's better at checking ranges and throwing out the ones that don't fit. Better to have a problem getting the wrong data into the database than trying to get it back out a couple years down the road. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Creating constraint sometime fail in a transaction
Hi, I've a strange bug on PgSQL 7.3.3 with function, constraints and transactions. As the foreign key are too heavy for a massive insert, even set defered, we've decided to put them in a function in order to enable/disable them. So, in such a transaction, first we disable the constraints, using a function, then inserting all the data, and afterall put the constraints back with another function. This work fine and quickly... if we do it once. But, when we tried to make a big batch with a lot of such transaction, sometime, some constraint aren't put back or drop, and the rest of the transactions falls. So, we're wondering if the action for putting/removing constraint could be executed out of the transaction, so that it may overlaps and crash in such case. Does anyone notice the same problem? -- Signature en cours de maintenance, Veuillez patienter... ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] create function atof?
Hello, Is it possible to create a database function that mimics the C function atof? I'm guessing it should look something like this: create function atof(varchar) returns float as '??' language returns null on null input; Thanks, Mark ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] postgresql multiple insert slow
Hello,
I've got a table in an oracle database with approx. 10 records, that
I'd like to put into a table in a postgresql database. (This should be
done a couple of times per week)
I have written a short perl script, on a server that has remote access
to both the oracle database as well as the postgresql database. I am
running postgresql 7.4.1 on FreeBSD.
My perl script looks something like this:
[...]
my $sth2 = $cnx2->prepare('SELECT * FROM oracle_table');
my $res2 = $sth2->execute();
while(my($field2,$field5,$field6) = ($sth2->fetchrow_array)) {
if(defined($field2)) {
my $sth = $cnx->prepare('INSERT INTO
the_pg_table(field1, field2) VALUES(?,?)');
my $result = $sth->execute($field2,$field5);
$sth->finish;
}
}
[...]
I runs fine - and I get no errors - but it takes almost 25 minutes to
complete.. I tried running the script while just grabbing the rows from
the oracle database and writing to a text file - and then it only takes
a couple of minutes .. So it must be the INSERT command that chokes - is
there a better way to do it ?
Any advise much appreciated.
/mich
--
Best Regards,
Michael L. Hostbaek
*/ PGP-key available upon request /*
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] create function atof?
> Is it possible to create a database function that mimics the C function atof? > I'm guessing it should look something like this: You can do this to convert a string to a float: select '3.14'::float + 1; ?column? -- 4.14 (1 row) Is that what you want? Yasir ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] searching polygons
You should use some variation of overlaps or contains within. There is some discussion and a list of operators in Issue #61 of General Bits. ( http://www.varlena.com/GeneralBits/61 ) I would also suggest looking at the geometric operators in the documentation. You may have to cast the polygon to a circle to use the operators, but it will still tell you whether the smaller polys are contained within or overlap the larger. elein On Tue, Feb 17, 2004 at 07:01:51PM -, David wrote: > What query would i have to use to search for an item using a polygon as a > parameter? (i.e a very large polygon that would identify smaller polygons > within it) ideally i would like to give postgresq a series of co-ordinates > and then have it return all those results whose polygons fall into that set > of co-ordinates, is this possible? > > at the moment all i can think of is > > > select * from species where location between '(0,0)' and '(1000,0)' and > '(0, 1000)' and '(1000; 1000)'; > > I think im way off, any suggestions? > > Cheers Dave > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
