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

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

[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 o

[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 k

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 table

[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 Po

[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 looke

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 Lawre

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

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)