On Thu, Nov 7, 2013 at 2:09 PM, Daniel L. Miller <[email protected]> wrote:
> Is it possible to refer to internal structures by reference in PSQL? Err, reference, usually yes. Change? Probably not. Basically, PSQL is limited to DML - not DDL. > > > create procedure P_BEFORE_INSERT > ( SEQ_NAME varchar(50), > TABLE_NAME varchar(50) ) > as > declare max_id type of integer; > declare seq_stmt varchar(50); > BEGIN > /* Example of hyper-paranoid processing */ > /* First determine if either no new id set or invalid id */ > if ((new.id is null) or (new.id = 0)) then begin > /* No valid new id passed - so obtain next available id */ > new.id = next value for :SEQ_NAME; > end > /* Now triple-check validity of sequence. There should be no */ > /* earthly reason for this - unless certain programmers decide */ > /* to poke values in - so we might delete this logic later */ > select max(id) from :TABLE_NAME into max_id; > if (new.id <= max_id) then begin > new.id = max_id + 1; > Probably a bad idea. Concurrent executions of those statements will lead to duplicate values for id. Unless you've got a descending index, max(id) is going to be really slow. > /* There is a legacy Firebird function call, still supported */ > /* to set a sequence value (formerly known as a generator */ > /* However, we're going to use modern SQL syntax and refer to */ > /* these as sequences and use the new SQL commands for working */ > /* with them. ALTER commands are not directly available within */ > /* stored procedures - but the EXECUTE STATEMENT lets us get around that */ > seq_stmt = 'alter sequence ' || :SEQ_NAME || ' restart with ' || > new.id; > Alter is a DDL statement. > execute statement seq_stmt; > end > end > > > So no, what you're trying to do isn't going to work. Good luck, Ann
