Re: INSERT Trigger to check for existing records : Does this do what I hope it is doing? - Mailing list pgsql-general

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 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.dealdidSELECT) 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
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