Subject Re: [firebird-support] Table and Sequence names by reference
Author Ann Harrison
On Thu, Nov 7, 2013 at 2:09 PM, Daniel L. Miller <dmiller@...> 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) )
    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;

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 ' ||;

Alter is a DDL statement.
      execute statement seq_stmt;

So no, what you're trying to do isn't going to work. 

Good luck,