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) )
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