Subject Table and Sequence names by reference
Author Daniel L. Miller
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

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.


create procedure P_BEFORE_INSERT
( SEQ_NAME varchar(50),
TABLE_NAME varchar(50) )
declare max_id type of integer;
declare seq_stmt varchar(50);
/* Example of hyper-paranoid processing */
/* First determine if either no new id set or invalid id */
if (( is null) or ( = 0)) then begin
/* No valid new id passed - so obtain next available id */ = next value for :SEQ_NAME;
/* 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 ( <= max_id) then begin = 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 ' ||;
execute statement seq_stmt;