Received: from chronozon.dyndns.org (cr466046-a.etob1.on.wave.home.com [24.112.191.209]) by hub.org (8.9.3/8.9.3) with ESMTP id QAA57925 for ; Tue, 23 May 2000 16:16:53 -0400 (EDT) (envelope-from stef@chronozon.dyndns.org) Received: by chronozon.dyndns.org (Postfix, from userid 99) id 4210E1F57C; Tue, 23 May 2000 16:16:01 -0500 (CDT) From: "Steffers" To: pgsql-sql@postgresql.org Reply-To: stef@chronozon.dyndns.org Subject: Functions and Variables. X-Mailer: NeoMail 0.95 X-IPAddress: 204.148.73.2 Message-Id: <20000523211601.4210E1F57C@chronozon.dyndns.org> Date: Tue, 23 May 2000 16:16:01 -0500 (CDT) X-Archive-Number: 200005/109 Hello, First let me congratulate everyone on Postgres 7. It is (functionality wise) MUCH richer than Sybase 12. I am in the midst of porting across an old (well. oldish) web database application to Postgres from Sybase, and in everything apart from Speed Postgres wins (then again, that is probably why Sybase wins out. It doesnt have any 'nice' features. gah!). the problem arises when I tried to translate a relatively simple trigger from Sybase to Postgres. The Sybase code looks like this : create trigger upd_client_hist on client for insert as declare @historyid integer declare @inserted_client_id varchar(16) declare @inserted_date_id datetime select @inserted_client_id=CLIENT_ID from inserted select @inserted_date_id=CHANGE_DATE from inserted select @historyid=max(history_id)+1 from client where CLIENT_ID=@inserted_client_id update client set history_id=@historyid where CLIENT_ID=@inserted_client_id and CHANGE_DATE=@inserted_date_id go (the go at the end jst executes the creation process). I looked through all the documentation on Postgres and decided to make it into a function. However, since this is a function I wanted to make the function 'generic' to a degree. so i tried this: CREATE FUNCTION upd_history_no_location(text,varchar(16)) RETURNS integer AS 'SELECT max(history_id)+1 FROM $1 where CLIENT_ID=CAST($2 as varchar(16));' LANGUAGE 'sql'; and that jst plain doesnt compile. i have tried and installed the plpgsql language as well on the database, but it doesnt seem to help any either. I plan on tying this into a trigger on INSERT (there is no update allowed on the table). I cant use an autoincremental field, as i need each new 'client_id' to start off with a history of 1, and keep its own 'unique history field'. its a historical database tied into a client workorder schema. Any help ? does this make sense even ;D many thanks, Stefs