Re: "SQL" REPLACE SYNTAX

On Wed, 2002-01-23 at 16:56, Christopher Kings-Lynne wrote:
> > Why so many? It is simple enough to attempt a delete and then do an
> > insert.
>
> DELETE WHERE what? Alternatively you can attempt and update then do an
> insert - question is UPDATE WHERE what?
>
> If the insert fails you need to select from the postgres system catalogs to
> find the primary key on the table (if there is one). Next, you need to find
> the fields that that primary key is defined over. Next, you need to parse
> the insert statement and find the fields being inserted that match the
> primary key. Lastly you need to modify the insert statment to an update
> statment, take out the values that are part of the primary key and make them
> part of the where clause of the update or delete statement.

I realise this wanders far from the SQL standard, but it's one of those
areas where the standard is awkward from an application programmer's
point of view. I would certainly love to see some syntax of this kind
available. If having to work out the primary key from schema queries is
too much work, then why not have a syntax that explicitly coped - after
all the whole thing is non-standard, so we don't have to follow MySQL's
lead on it:

INSERT OR REPLACE <table> ( <field list> )
VALUES( <value list> ) WHERE <condition>

would be just fine by me.

There are many places in my programs where I want to do this sort of
INSERT OR REPLACE functionality and the ability to do so would make my
code more maintainable, and much less prone to stupid errors.

More simply (?) I want to be able to build the fields / values for my
query and then decide at the last minute whether I was doing an INSERT
or an UPDATE.

To do this (without throwing an error) I currently do a "SELECT ...
WHERE <condition>;" and then construct my SQL appropriately. When I add
a new column to a table (this is the real world :-) I frequently forget
to maintain one branch of the resulting code building the SQL statement.

It is this need that Manuel is catering for by implementing the
functionality in Metabase, but I expect it would be more efficient to
implement it in PostgreSQL natively.

Cheers,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?