destructive "SELECT" query for SQL?

Dr. Hawkins dochawk at gmail.com
Mon Oct 7 00:05:08 CEST 2013


On Sun, Oct 6, 2013 at 10:39 AM, Peter Haworth <pete at lcsql.com> 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