Re: [SQL] UPDATE comparing dates and non-dates data

2002-12-13 Thread Bret Hughes
On Fri, 2002-12-13 at 11:43, javier garcia wrote: > Hi; > I've got date data, extracted from rain gauge stations. The date of a row in > my data are structured in three integer fields, and as a result of a query I > can get the following (what is quite good for my): > cod_station | year | month

Re: [SQL] UPDATE comparing dates and non-dates data

2002-12-13 Thread Josh Berkus
On Friday 13 December 2002 09:43, javier garcia wrote: > Hi; > I've got date data, extracted from rain gauge stations. The date of a row in > my data are structured in three integer fields, and as a result of a query I > can get the following (what is quite good for my): > cod_station | year | m

Re: [SQL] A PL/PgSQL Question

2002-12-13 Thread Josh Berkus
Ludwig, >What is the difference between the first and second > chunk PL/PgSQL INSERT/UPDATE trigger function code > aside from the fact that first scenario will result in > an "ABORT" state? > > Are there any instances where a NOTICE and a RETURN > NULL statement is a much better than a RA

Re: [SQL] server terminated by a query in 7.3

2002-12-13 Thread Jie Liang
Maybe you did a patch in localbuff.c to fix that vacuuming temp table? Jie liang -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Friday, December 13, 2002 10:12 AM To: Jie Liang Cc: [EMAIL PROTECTED]; '[EMAIL PROTECTED]' Subject: Re: server terminated by a query in 7.3

Re: [SQL] A Costly function + LIMIT

2002-12-13 Thread Tom Lane
Chris Gamache <[EMAIL PROTECTED]> writes: > It runs "widget" on 1 records. The damage would be negligible if it could > run on the 100... So do the function calculation outside the UNION/ORDER BY. SELECT name, address, city, state, zip, widget(name, address, city, state, zip) FROM ((SELECT *

[SQL] A Costly function + LIMIT

2002-12-13 Thread Chris Gamache
PostgreSQL 7.2.3 I have a function that is quite costly to run on 1000's of records... Let's call it "widget". I have a query (SELECT name, address, city, state, zip, widget(name, address, city, state, zip) FROM eastern_usa ORDER BY state, city, zip, name LIMIT 5000) UNION ALL (SELECT name

Re: [SQL] server terminated by a query in 7.3

2002-12-13 Thread Tom Lane
Jie Liang <[EMAIL PROTECTED]> writes: > however, after > I upgrade my postgresql from 7.2 to 7.3, one query always makes server > terminated, could > you give me a solution for it? I cannot reproduce this with the information you gave. regards, tom lane --

[SQL] A PL/PgSQL Question

2002-12-13 Thread Ludwig Lim
Hi: I have the following PL/PgSQL code blocks: a) IF (condition) THEN RAISE EXCEPTION ''Cannot Insert''; END IF; b) IF (condition) THEN RAISE NOTICE ''Cannot Insert''; RETURN NULL; END IF; What is the difference between the first and second chunk PL/PgSQL INSERT/UPDA

[SQL] UPDATE comparing dates and non-dates data

2002-12-13 Thread javier garcia
Hi; I've got date data, extracted from rain gauge stations. The date of a row in my data are structured in three integer fields, and as a result of a query I can get the following (what is quite good for my): cod_station | year | month | day | rain -+--+---+-+-- 7250

[SQL] server terminated by a query in 7.3

2002-12-13 Thread Jie Liang
Tom, I've a perl script, which has been used for a long time, it works well, however, after I upgrade my postgresql from 7.2 to 7.3, one query always makes server terminated, could you give me a solution for it? Thanks. Jie Liang SELECT urlinfo.id,url,iprism_map,iprism_map_sg,level,domid, CASE

Re: [SQL] order by and limit with multi-column index, bug?

2002-12-13 Thread Harald Krake
On Friday 13 December 2002 03:47 pm, Jakub Ouhrabka wrote: > hi, > > could it be that "order by col1, col2 desc" is different from "order by > col1 desc, col2 desc" ? these are different and it's correct. > > i'm not sure if this is your problem since i haven't digged into it... > sorry... but chec

Re: [SQL] order by and limit with multi-column index, bug?

2002-12-13 Thread Jakub Ouhrabka
hi, could it be that "order by col1, col2 desc" is different from "order by col1 desc, col2 desc" ? these are different and it's correct. i'm not sure if this is your problem since i haven't digged into it... sorry... but check it to be sure... hth, kuba On Fri, 13 Dec 2002, Harald Krake wro

[SQL] order by and limit with multi-column index, bug?

2002-12-13 Thread Harald Krake
as a workaround for min()/max() on indexed columns forcing an index-scan I tried "order by" with "limit 1". Works fine for the first record but fails for the last. I don't know why. Here's the setup: A table "journal" containing several million records with an index "CREATE INDEX journal_kblatts

Re: [SQL] Stored Procedure Problem

2002-12-13 Thread Rajesh Kumar Mallah.
In 7.3 you can , in follwoing steps, 1. do a CREATE TYPE (i would recommend to use a sperate schema for storing user defined types) 2. in plpgsql declare the RECORD of that type . 3. populate the record varible according to your business logic and return the RECORD using RETURN statements.

Re: [SQL] function replace doesnt exist

2002-12-13 Thread Rajesh Kumar Mallah.
Are you looking for this ? available on http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=23 regds mallah. View One Recipe Home -> Postgres -> CookBook Home -> View One Recipe Submitted on: 03-16-2001 Description: mimic oracle's replace function. versions in pltcl