Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8CFnc719939 for ; Wed, 12 Sep 2001 11:49:38 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f8CFjFZ08761 for pgsql-sql@postgresql.org; Wed, 12 Sep 2001 11:45:15 -0400 (EDT) (envelope-from news) From: "Peter Schmidt" X-Newsgroups: comp.databases.postgresql.sql Subject: plpgsql function case statement Date: Wed, 12 Sep 2001 08:46:34 -0700 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 60 Message-ID: <9nnvu8$8hk$1@news.tht.net> Reply-To: "Peter Schmidt" X-Complaints-To: scrappy@hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 To: pgsql-sql@postgresql.org X-Archive-Number: 200109/63 X-Sequence-Number: 4709 I'm having trouble finding the best solution for the following plpgsql function. The function does the following: - gets an integer from table1 (using function getHighMark) - Updates info.lastused with the latest timestamp from usage_log where info.id = usage_log.id, but only if the sequence number in usage_log is greater than or equal to what was returned by getHighMark. - if the timestamp value returned by the subselect is null, I want to set info.lastused to an exisiting value from another timestamp column in info(firstused). Without the case statement the update inserts null into each row where criteria was not met. rows_updated is a placemarker for later postgres version...we're on 7.0.3 One alternative we had thought of was to create a view inside the function (and destroy it) that is based on seq_number, but CREATE VIEW inside the function seems to fail when it includes the variable "wm". Here's what I came up with...but it's clearly not very efficient... Can anyone help? TIA Peter create function updateLastUsed(text, text) returns integer as ' declare wm integer; rows_updated integer; begin rows_updated := 0; wm := getHighmark($1,$2); UPDATE info SET lastused = case when (SELECT MAX(p.requesttime) FROM usage_log p WHERE p.id = info.id AND p.seq_no >= wm) = null then firstused else (SELECT MAX(p.requesttime) FROM usage_log p WHERE p.id = info.id AND p.seq_no >= wm) end; return rows_updated; end; ' language 'plpgsql';