On 11/10/2013 12:39 AM, Mark Rotteveel wrote:

> On 10-11-2013 09:29, Daniel L. Miller wrote:
>> On 11/10/2013 12:10 AM, Mark Rotteveel wrote:
>>> The Firebird syntax doesn't allow an INTO in this context. Technically
>>> Firebird has two selects, one with the INTO clause and one without. The
>>> SELECT with INTO clause is only allowed in PSQL as a separate statement,
>>> or as part of a FOR SELECT ... DO.
>>>
>>> An EXISTS only allows a 'normal' select (that is: without an INTO clause).
>>>
>>> Mark
>> Fair enough. In that case, which of the following would be "better" ?
>> The context is a procedure call to update the city entry - if a ID is
>> passed to the procedure then it simply modifies the existing record.
>> The logic under discussion is for
>> insert - but with protection to avoid duplicates.
>>
>> Option 1:
>> if ( exists( select ID from CITIES where CITY = :NEWCITY ) ) then begin
>> select ID from CITIES where CITY = :NEWCITY into :NEWID;
>> suspend;
>> end
>> /* Do insert operation since nothing found */
>>
>> Option 2:
>> select ID from CITIES where CITY = :NEWCITY into :NEWID;
>> if ( NEWID is not null ) then suspend;
>> /* Do insert operation since nothing found */
>>
>> Option 3:
>> select ID from CITIES where CITY = :NEWCITY into :NEWID;
>> if ( NEWID > 0 ) then suspend;
>> /* Do insert operation since nothing found */
>>
>> Option 4 (since this is a two-column table):
>> insert or update into CITIES ( CITY ) values ( :NEWCITY ) matching ( CITY ) returning ID into :NEWID;
>> /* This theoretically (in my theory, anyway) either practically does nothing if it exists
>> (might result in activating update triggers and last modified timestamps), otherwise add the entry */
>>
>> Or is there yet another better construct?
>>
>> In typing this out I think I've answered my own question - I would say Option 1 is the least efficient as it has two selects, and while Option 4 is expressed the simplest it may result in side-effects - so choose either Option 2 or 3 depending on personal preference. So I'm leaning towards Option 3 unless I'm advised differently.
>>
> I'd go for a MERGE
> (http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-merge.html)
> or maybe UPDATE OR INSERT
> (http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-update-or-insert.html).
>
> BTW: Unless you explicitly want to create a selectable stored procedure,
> I don't think you need (nor want) to use SUSPEND in this stored procedure.
>

I don't think MERGE will work for me - I'm poking in values not joining
tables. However, UPDATE OR INSERT does provide a rather elegant answer
- thank you. But that brings up another question:

Starting at the lowest level, I've got a basic two-column table:
CREATE TABLE ADDR_CITIES (
ID D_ID NOT NULL,
CITY D_ADDR_CITY NOT NULL,
PRIMARY KEY (ID),
UNIQUE (CITY)
);
CREATE UNIQUE DESCENDING INDEX IDX_ADDR_CITIES_DESC ON ADDR_CITIES (ID);

Standard before-insert trigger:
CREATE TRIGGER T_BI_ADDR_CITY FOR ADDR_CITIES ACTIVE
BEFORE INSERT POSITION 0
AS BEGIN
if ((new.id is null) or (new.id < 1)) then
new.id = next value for S_CITY;
END;

So - if I haven't totally blown it (which is always a possibility), I
now have a two-column table, with a numeric primary ID associated with a
trigger-driven sequence and a unique varchar column. So...now the
question is updates:
CREATE PROCEDURE P_SET_ADDR_CITY (
ID TYPE OF D_ID,
CITY TYPE OF D_ADDR_CITY )
RETURNS (
NEWID TYPE OF D_ID )
AS
BEGIN
update or insert into ADDR_CITIES ( ID, CITY )
values ( :ID, :CITY )
matching ( ID )
returning ID into :NEWID;
suspend;
END

If I'm correct, then calling this function with "select NEWID from
P_SET_ADDR_CITY( 0, 'My City' );" will result in:
1. No match for 0 should be found (as my trigger-driven sequence should
ensure).
2. The insert operation will activate the trigger, assign a new primary
key, and add the new record.

Assuming the result of the last operation yielded NEWID=5, then "select
NEWID from P_SET_ADDR_CITY( 5, 'My Other City' );" will result in:
1. 5 should be matched.
2. 'My City' changed to 'My Other City'.
3. NEWID will return 5 again.

So far, so good. Now, what happens if - for some bizarre inexplicable
reason - a call is now made with "select NEWID from P_SET_ADDR_CITY( 0,
'My Other City' );"
1. No match for 0 should be found - so insert operation attempted.
2. The CITY column has a unique constraint - so the insertion will fail.

So...what happens next? Is NEWID set to any useful value? I should
mention I will be calling this from PHP if it makes any difference. I'm
trying to determine where and how much exception handling to place for
this - do I build it into my set procedure? Or trap for it in the PHP
processing? Or both? I would think safe practices would dictate both
but I'd like guidance here.

--
Daniel