SQL: using the newly inserted index as a value for insertion

[Dr. Hawkins]

On Sat, Nov 23, 2013 at 5:01 PM, Peter Haworth <pete at lcsql.com> wrote:

> Not sure I understand what you are trying to do here. Are you trying to set
> a literal value into the cmd column?  If so, you need single quotes around
> it.  If you are trying to execute an INSERT command to get the value of
> cmd, you can't do that in an UPDATE command.
>

The cmd is used to re-create the data in the in-memory db (this let me go
to 1 remote table per debtor, rather than 3 or more).  The catch is that as
I write the command, and send it to the remote, I don't know what value
will be assigned.

If I use postgres' "returning" extension, I can get the index of the new
entry (but aside from non-portability, the transaction would be over, and I
can't get multiple queries back, and . . . .).

I'm trying to use the column name of the unique key to put the value of the
key into the middle of a string in the same record.

so

    INSERT INTO . . .
      <creates a record with a key and a scratch-field that I can find >
      <the unique key now exists, at least for the rest of the transaction >
    UPDATE . . . SET cmd="blah-blah" WHERE {scratch matches}
        <tries to use the key created in insert, and stick it into the
middle
        < of the blah-blah tring>


What is the intention of the ' || key || ' in the VALUES?
>

concatenation.  Something got eaten . . .  That *should* have been,

UPDATE remoteTable SET cmd='INSERT INTO localTable (key,stuff)
   VALUES(' || key || ',  ''this is some stuff'' ); ' WHERE
scratch='scratchKey';

so that a query would produce a cmd of
  INSERT INTO localTable (key,stuff)
      VALUES(<the assigned key here>,  'this is some stuff' ) ;



> Plus your UPDATE
> command names two columns but there is only one value in the VALUES clause.
>
(same glitch)



>
> The best way to do this would be to create a TRIGGER to execute AFTER
> INSERT into your remoteTable table which would update the cmd column for
> you.  That way, you would only have to issue the INSERT command and the
> UPDATE would be handled by the trigger.  You can refer to any values from
> the INSERTed remoteTable entry with the syntax new.columname.
>

That could be an ugly (and expensive?) replacement, couldn't it--I'd be
trying to match a "magic string" I left in the middle of a string with an
integer.

Or am I missing something?


-- 
Dr. Richard E. Hawkins, Esq.
(702) 508-8462