Re: INSERT Trigger to check for existing records : Does this do what I hope it is doing?

On 11/25/20 8:43 AM, hagen(at)datasundae(dot)com wrote:
> Adrian,
>
> Thanks for your detailed response. That's very kind and much appreciated.
>
> 1. OK that's just me groping for a RETURN statement that doesn't throw a rod.

Things can still work, sort of. I once cranked up and ran(for a short
time) a JD 4020 that had a rod coming through the block. It was smoky
and rough, but it ran. OT I know but that image came back clear as day.

I don't actually need to return anything as the goal of the FUNCTION
(for the moment) is to perform updates to a table. It might be nice to
return some sort of confirmation but it's not necessary. Apparently I
don't fully understand the RETURN concept (oh really? 😉. Any
suggestions where to research or read?
>
> 2. I have two tables:
> a) sfdc which is the baseline - doesn't change - isn't updated by this FUNTION
> b) hygiene_119 a new table which has some records (~80%) which are identical to those already in sfdc.
>
> The logic flow is:
> i) SELECT the dealids from hygiene_119 (latest or new report dated 11/9)

Not seeing where that is done?

> ii) compare those hygiene_119.dealids with the existing sfdc.dealids - hence the IF $1 (one result from the hygiene_119.dealdid SELECT) is IN (matches) any of the sfdc.dealids THEN

Again not seeing any comparison to sfdc?

> iii) UPDATE hygiene_119 SET status = 'SAME' WHERE dealid = $1; --flag that hygiene_119 record as the SAME or a duplicate record
> iv) ELSE UPDATE hygiene_119 SET status = 'NEW' WHERE dealid = $1; --flag that hygiene_119 record as NEW or a new record
> Once I have inspected the "NEW" records in hygiene_119 I will INSERT then into sfdc. Then rinse and repeat each week with a new report.

Until the previous questions are addressed the above is not doable.

>
> 3. Not positive the IF is doing what I want, but if I copy a sfdc.dealid into the same_test() parameter field the FUNTION does update the hygiene_119.status field properly. To me, it appears I just need a way to iterate through and insert one hygiene_119.dealid in the same_test parameter field. Then the UPDATE should flag all the hygiene_119 records as SAME or NEW. Obviously I don't REALLY need both flags as the absence of a flag would indicate status too.

Before continuing with the function I would try some SELECT functions
that do what you want.

>
> Does that articulate the thought process adequately?
>
> Best,
>
> Hagen
>
>
> -----Original Message-----
> From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> Sent: Wednesday, November 25, 2020 9:07 AM
> To: Hagen Finley <hagen(at)datasundae(dot)com>; pgsql-general(at)lists(dot)postgresql(dot)org
> Subject: Re: INSERT Trigger to check for existing records : Does this do what I hope it is doing?
>
> On 11/25/20 7:41 AM, Hagen Finley wrote:
>> Folks,
>>
>> Just a quick question. *Using this FUNCTION:*
>>
>> CREATE OR REPLACE FUNCTION same_test(did numeric)
>> RETURNS numeric AS $$
>> BEGIN
>> IF $1 IN
>> (SELECT dealid from sfdc)
>> THEN
>> UPDATE hygiene_119 SET status = 'SAME';
>> ELSE
>> UPDATE hygiene_119 SET status = 'NEW';
>> END IF;
>> RETURN NULL;
>> END;
>> $$ LANGUAGE plpgsql;
>
> The above is broken in multiple ways:
>
> 1) You have RETURNS numeric and then RETURN NULL; This means you will not actually return anything
>
> 2) You have the input argument did but you never use it to restrict your UPDATEs.
>
> 3) Not sure the logic in the IF actually works even if you filtered by did. This assumes that there will always be a row in hygiene_119 that matches one in hygiene_112. Given that you setting a 'NEW' flag I'm guessing that is not the case.
>
> You will need to sketch out the thought process at work here before we can go any further with this.
>
>
>
>>
>> *Does the following query input the the dealids that result from the
>> SELECT statement into the parameter of the sames_test() FUNCTION?*
>
>>
>> Select dealid sametest(dealid) FROM hygiene_123;
>
> Have no idea what that is supposed to do?
>
> If you want to use the function(after fixing it) you would have to do:
>
> select * from some_test(some_number);
>
>>
>> I doubt it does (my query runs a /long time)/ :-). I know I can utilize
>> python to push SELECT results into a array and then run a 'FOR d in
>> dealids' LOOP to feed the FUNCTION parameter but I'd like to learn how
>> to do that with nested SQL statements or FUNCTIONS.
>>
>> Thanks!
>>
>>
>> Hagen
>>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com