destructive "SELECT" query for SQL?

Dr. Hawkins dochawk at
Sun Oct 6 18:05:08 EDT 2013

On Sun, Oct 6, 2013 at 10:39 AM, Peter Haworth <pete at> wrote:

> Not sure I fully understand but you can use the literal NULL in a SELECT
> statement, eg SELECT col1,col2,NULL FROM.....

I create a record in a sqlite :memory:, and give it a tentative id (a
negative number).  It is conceivable that a couple of these accumulate
before synchronizing with the remote/master db.

I insert this information into the master, which causes a serial number to
be assigned on the primary index.

As part of the same transaction, I have a select which pulls this id back.
I use a field not used for this type of data to store a unique identifier
for the running program and the temporary id, allowing me to map the
universal uniq id to the in-memory database.

After the SELECT that brings this back, I would like that field to be
emptied in the postgres database.

At the moment, on the readback, I just leave an in-memory marker so that it
will wipe the field next time there is a a contact with the remote db, but
it would be nice to get it from the same transaction.

The funky INSERT I was thinking about would happen as something like

   INSERT INTO masterDb (myData,)  VALUES ("abc123") RETURNING
(serialNumber, -7)

where -7 was my temporary tag.

The problem is that if I follow this with a SELECT, even if UNION
compatible, I don't seem to get the return value.

(again, for those joining late, the lag of the remote transaction is
presumed to be the "expensive" part of the transaction)
Dr. Richard E. Hawkins, Esq.
(702) 508-8462

More information about the Use-livecode mailing list