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

[Hagen] Answers inline

-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Wednesday, November 25, 2020 10:13 AM
To: hagen@datasundae.com; pgsql-general@lists.postgresql.org
Subject: 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@datasundae.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
cameback 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
theRETURN 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?

[Hagen] I was hoping to do the SELECT from hygiene_119 when I called the FUNCTION same_test() by SELECTING
hygiene_119.dealidand then using that list as an input via same_test (hygiene_119.dealid) 

[Hagen] SELECT dealid sametest(dealid) FROM hygiene_123; (more precisely SELECT hygiene_119.dealid,
same_test(hygiene_119.dealid)FROM hygiene_119;  ). 

>         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?

[Hagen] Assuming the same_test(hygiene_119.dealid) call worked - IF $1 = hygiene_119.dealid[0] in python array
vernacularwould be compared against the list of the SELECT sfdc.dealid results. 

[Hagen] Spelling it out more clearly isn't exactly boosting my confidence in my approach 😉

>         iii) UPDATE hygiene_119 SET status = 'SAME' WHERE dealid = $1; --flag that hygiene_119 record as the SAME or
aduplicate record 
>         iv) ELSE UPDATE hygiene_119 SET status = 'NEW' WHERE dealid = $1; --flag that hygiene_119 record as NEW  or a
newrecord 
>     Once I have inspected the "NEW" records in hygiene_119 I will INSERT then into sfdc. Then rinse and repeat each
weekwith 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
FUNTIONdoes update the hygiene_119.status field properly. To me, it appears I just need a way to iterate through and
insertone hygiene_119.dealid in the same_test parameter field. Then the UPDATE should flag all the hygiene_119 records
asSAME 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.klaver@aklaver.com>
> Sent: Wednesday, November 25, 2020 9:07 AM
> To: Hagen Finley <hagen@datasundae.com>;
> pgsql-general@lists.postgresql.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
rowin 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.klaver@aklaver.com