Is it possible to refer to internal structures by reference in PSQL? My
goal - instead of repetively coding the standard before-insert triggers
for using generators/sequences, I want to use a procedure and have the
triggers call the procedure with the desired sequence & table to be
affected.
I'm probably missing something else - like the fact that the "new"
references probably aren't available in the procedure, so I'll need to
do some additional processing - but my direct question is regards to
trying to use names by reference in procedures.
Example:
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;
/* 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;
execute statement seq_stmt;
end
end
--
Daniel
