Re: [SQL] Getting the output of a function used in a where clause
try: SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes where distance <= $dist;â; OR you could use a gist index with a geometric datatype to get it a lot faster. On Sat, 09 Apr 2005 03:43:39 +0200, Bill Lawrence <[EMAIL PROTECTED]> wrote: HI, Iâm a newbie so please bear with me. I have a function defined (got it from one of your threadsâ thanks Joe Conway) which calculates the distance between 2 zip code centeroids (in lat,long). This thing works great. However, I want to sort my results by distance without incurring the additional burden of executing the function twice. A simplified version of my current SQL (written in a perl cgi) that returns a set of zip codes within a given radius is: What I want to write is something like: $sql = âSELECT zipcode, distance from zipcodes where distance <= $dist order by distance;â; But I donât the magic SQL phrase to populate the distance variable using my nifty function. Do I need to create an output type for distance? Thanks in advance! Bill ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problems with Set Returning Functions (SRFs)
On Apr 6, 2005, at 2:53 PM, Otto Blomqvist wrote: secom=# select f1, f2, f3 from testpassbyval(1, (Select number1 from test)); ERROR: more than one row returned by a subquery used as an expression This is where I fail. Am I even on the right path here ? Writing the actual parsing function will be easy once I have a working concept. How about (untested): select f1, f2, f3 from ( select testpassbyval(1,( select number1 from test) ) ) a; Sean ---(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] How to Port Oracle's user defined "Package" into Postgres 8.0.1.
Title: How to Port Oracle's user defined "Package" into Postgres 8.0.1. Hi folks, Can any one give me an idea about: How to Port Oracle's user defined "Package" into Postgres 8.0.1. If possible pls attache sample code. Thanks Dinesh Pandey
[SQL] Update aborted if trigger function fails?
Hi, I just noticed this (odd?) behaviour, and it kind of scares me. For testing purposes, I put a deliberate syntax error; this wouldn't happen in a real-life situation. But what if the error gets triggered by something that happens later on? say, if the trigger function uses a field that later on gets dropped from the table -- something that was working fine all of a sudden produces syntax errors while executing the function (I just verified, to see if the server would let me drop a column that is being referenced by a function invoked by an active trigger, and it did let me drop it). Isn't this a little fragile? Is there something I could do to avoid this situation? Should trigger functions be extremely simple as to guarantee that an error would never happen? Thanks, Carlos -- ---(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] PGCrypto: Realworld scenario and advice needed
Hello all, I'm looking for advice on real-world PGCrypto usage. I understand how to programmatically encrypt/decrypt data with PGCrypto -- no problem. My question is: What is the best way to update massive amounts of *existing* encrypted data with a new encryption passphrase, assuming you know the old passphrase? For example: Let's say that periodically there must be a one-time change of the encryption passphrase and ALL existing encrypted data has to be encrypted with the new passphrase. My initial attack plan was to do the following: 1. Call decrypt() with the old-passphrase to decrypt each table's existing data. 2. Temporarily store the decrypted data in temp tables. 3. Delete all rows of encrypted data from the original tables -- thereby clearing the tables of all data encrypted with the old passphrase. 4. Call encrypt() with the new passphrase to encrypt all data in the temp tables -- thereby repopulating the production tables with data encrypted with the new passphrase. 5. Blow away the temp tables. But this seems like a tedious procedure. Is there any simple way to update ALL existing encrypted data with a new passphrase, assuming you know the old passphrase and encryption type (i.e. AES, Blowfish, etc.) without having to go through the 5-step process mentioned above? Thank you and best regards, Michael Moran ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] PGCrypto: Realworld scenario and advice needed
Moran.Michael wrote: My initial attack plan was to do the following: 1. Call decrypt() with the old-passphrase to decrypt each table's existing data. 2. Temporarily store the decrypted data in temp tables. 3. Delete all rows of encrypted data from the original tables -- thereby clearing the tables of all data encrypted with the old passphrase. 4. Call encrypt() with the new passphrase to encrypt all data in the temp tables -- thereby repopulating the production tables with data encrypted with the new passphrase. 5. Blow away the temp tables. But this seems like a tedious procedure. Is there any simple way to update ALL existing encrypted data with a new passphrase, assuming you know the old passphrase and encryption type (i.e. AES, Blowfish, etc.) without having to go through the 5-step process mentioned above? Why not use a single UPDATE command, e.g. something like: UPDATE tbl SET f1 = encrypt(decrypt(f1, 'oldkey', 'aes'), 'newkey', 'aes'); Joe ---(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] Query runs very slowly in Postgres, but very fast in other DBMS
Tables: CREATE TABLE dok ( dokumnr NUMERIC(12), CONSTRAINT dok_pkey PRIMARY KEY (dokumnr) ); CREATE TABLE rid ( dokumnr NUMERIC(12) ); CREATE INDEX rid_dokumnr_idx ON rid (dokumnr); Query: SELECT dokumnr FROM rid WHERE dokumnr NOT IN (select dokumnr FROM dok); runs VERY slowly in Postgres. It uses the following query plan: Seq Scan on rid (cost=0.00..28698461.07 rows=32201 width=14) Filter: (NOT (subplan)) SubPlan -> Seq Scan on dok (cost=0.00..864.29 rows=10729 width=14) In Microsoft Visual FoxPro this query runs fast. FoxPro uses indexes speed up the query by comparing bitmaps. Is it possible to speed up this query is Postgres ? How to force Postgres to use indexes for this query ? Andrus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] OpenFTS
Hoping someone can provide feedback on integrating OpenFTS into Postgres. I am running pg 7.3.4 and have a large database of articles that are constantly changing ( being added, updated, removed). I want to perform key word searches across these articles, ranked by relevance and have looked at a number of solutions, including swish-e and ht://dig, but OpenFTS looks like an great solution -- if it works well. Is anybody using OpenFTS with their pg db and if so how does it perform? Did you encounter any problems installing? Any drawbacks? Many thanks to anyone with the time to respond, Dan Dan Feiveson Broomfield, Colorado
Re: [SQL] Getting the output of a function used in a where clause
Boy I sure thought that would work... I received the following from postgres: ERROR: Attribute "distance" not found. Started looking into gist Looks complex. Any other ideas? -Original Message- From: PFC [mailto:[EMAIL PROTECTED] Sent: Monday, April 11, 2005 1:51 AM To: Bill Lawrence; pgsql-sql@postgresql.org Subject: Re: [SQL] Getting the output of a function used in a where clause try: SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes where distance <= $dist;"; OR you could use a gist index with a geometric datatype to get it a lot faster. On Sat, 09 Apr 2005 03:43:39 +0200, Bill Lawrence <[EMAIL PROTECTED]> wrote: > HI, > > I'm a newbie so please bear with me. I have a function defined (got it > from > one of your threads... thanks Joe Conway) which calculates the distance > between 2 zip code centeroids (in lat,long). This thing works great. > However, I want to sort my results by distance without incurring the > additional burden of executing the function twice. A simplified version > of > my current SQL (written in a perl cgi) that returns a set of zip codes > within a given radius is: > > > What I want to write is something like: > > $sql = "SELECT zipcode, distance from zipcodes where distance <= $dist > order > by distance;"; > > But I don't the magic SQL phrase to populate the distance variable using > my > nifty function. Do I need to create an output type for distance? > > Thanks in advance! > > Bill > > > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Query runs very slowly in Postgres, but very fast in other DBMS
"Andrus Moor" <[EMAIL PROTECTED]> writes: > Seq Scan on rid (cost=0.00..28698461.07 rows=32201 width=14) > Filter: (NOT (subplan)) > SubPlan > -> Seq Scan on dok (cost=0.00..864.29 rows=10729 width=14) > Is it possible to speed up this query is Postgres ? Can you switch to int4 or int8 instead of NUMERIC(12)? I think that we don't currently consider NUMERIC hashable, and you really need a hash subplan here. In general NUMERIC is a bad choice if you're hot about performance, anyway. Integers or floats would be probably two orders of magnitude faster. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Query runs very slowly in Postgres, but very fast in other DBMS
try this query : SELECT rid.dokumnr as d1 ,dok.dokumnr as d2 FROM rid left join dok on rid.dokumnr = dok.dokumnr where dok.dokumnr is null; > Tables: > > CREATE TABLE dok ( dokumnr NUMERIC(12), > CONSTRAINT dok_pkey PRIMARY KEY (dokumnr) ); > CREATE TABLE rid ( dokumnr NUMERIC(12) ); > CREATE INDEX rid_dokumnr_idx ON rid (dokumnr); > > Query: > > SELECT dokumnr FROM rid WHERE dokumnr NOT IN > (select dokumnr FROM dok); > > runs VERY slowly in Postgres. It uses the following query plan: > > Seq Scan on rid (cost=0.00..28698461.07 rows=32201 width=14) > Filter: (NOT (subplan)) > SubPlan > -> Seq Scan on dok (cost=0.00..864.29 rows=10729 width=14) > > In Microsoft Visual FoxPro this query runs fast. FoxPro uses indexes speed > up the query by comparing bitmaps. > > Is it possible to speed up this query is Postgres ? How to force Postgres > to use indexes for this query ? > > Andrus > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Krasimir Dimitrov IT Department AII Data Processing Ltd., 16 Ivan Vazov Str, Sofia 1000, Bulgaria Phone: +359 2 9376 352 E-mail: [EMAIL PROTECTED] http://www.see-news.com ---(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
