pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Steffers <stef@chronozon.dyndns.org>
To: pgsql-sql@postgresql.org
Subject: Functions and Variables.
Date: Tue, 23 May 2000 16:16:01 -0500 (CDT)
Message-ID: <20000523211601.4210E1F57C@chronozon.dyndns.org> (raw)

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



Message-ID: <20000523211601.4210E1F57C@chronozon.dyndns.org>
Permalink:  ../20000523211601.4210E1F57C@chronozon.dyndns.org/
Also on:    postgresql.org/message-id/20000523211601.4210E1F57C@chronozon.dyndns.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: stef@chronozon.dyndns.org
  Subject: Re: Functions and Variables.
  In-Reply-To: <20000523211601.4210E1F57C@chronozon.dyndns.org>
  References: <20000523211601.4210E1F57C@chronozon.dyndns.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox