On Fri, Nov 8, 2013 at 8:18 PM, Daniel L. Miller <dmiller@...> wrote:

On 11/7/2013 3:45 PM, Ann Harrison wrote:

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.

Ok - can you please clarify what I CAN do?  

You can change data, but not metadata. 

1.  From within an insert/update trigger, is there a variable that provides the table name? 

No.  As Sean pointed out, a trigger is associated with exactly one table, so you can safely

hard code the table name in the trigger.

 

Or, if I'm trying to use a common stored procedure, would I call it from the trigger using the table name as a literal string parameter?

Yes, if you have the name in the trigger.

 

2.  Even if the DDL statement I used was wrong (and I admit that - I pasted it without going through it first) can you show me a valid DML?  Something like "SELECT * FROM :tablename"?

 

In straight PSQL, variables cannot be used as table or column names.  You can get around that limit by building a statement and using EXECUTE STATEMENT to run it.  That has some slightly odd behavior in terms of security - my recollection is that the statement is run with the user's privileges not the privileges of the procedure.   The statement in an EXECUTE STATEMENT is not cached - it must be parsed, compiled, and optimized each time it runs.

3.  My overly paranoid belt-and-suspenders isn't going to remain in production code (I hope).  I had that both as a safeguard and as an example to another programmer I'm working with. 

It's not a great example either in Firebird or in most other databases unless they're running in serializable isolation mode.  When you get the max value for ID, Firebird returns the maximum value that was committed when your transaction started (in default isolation) or the most recently committed maximum value (in read-committed mode).  There may be other, higher values in the database.

Here's a case.  Values are 1, 2, 3 .... 158.  Transaction A stores 159.  Transaction B stores 160.  Transaction C asks for the maximum value and gets 158, so it tries to store 159.  Transaction A commits.  Transaction B commits.  If there's a unique index on the field, Transaction C gets an error, otherwise it stores a duplicate value.  That's the behavior of Multi-Version Concurrency Control.  Databases that use record locks will have the same behavior unless they're running in a mode that locks highest known value - in which case neither Transaction B nor Transaction C could store its record until A commits.  

So getting Max(key field) and adding 1 is inefficient and error prone in multi-user applications.  That's why I suggested that you not use it.

 

The production code would just have the "standard" check to see if an id was passed, and otherwise perform the assignment of "new.id = NEXT VALUE FOR sequence".  But I'd like to have that code structure in a stored procedure so my triggers are basically just one line.  Is that wrong?

Wrong is a pretty strong word.  Some people like to give fields names that include the table, table type, data type, and length.  That's not wrong, but it's pretty ugly and makes for a real mess when you decide to make the field a little bit longer.  A matter of taste, training, and corporate culture.  Putting the check for a missing id in a trigger means that the operation is parsed, complied and optimized once on the first insert rather than once per record.  So I'd settle for inefficient.  But tidy.

Good luck,

Ann