[Hagen] Answers inline -----Original Message----- From: Adrian Klaver <adrian.klaver@xxxxxxxxxxx> Sent: Wednesday, November 25, 2020 10:13 AM To: hagen@xxxxxxxxxxxxxx; pgsql-general@xxxxxxxxxxxxxxxxxxxx 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@xxxxxxxxxxxxxx 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? [Hagen] I was hoping to do the SELECT from hygiene_119 when I called the FUNCTION same_test() by SELECTING hygiene_119.dealid and 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 vernacular would 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 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.klaver@xxxxxxxxxxx> > Sent: Wednesday, November 25, 2020 9:07 AM > To: Hagen Finley <hagen@xxxxxxxxxxxxxx>; > pgsql-general@xxxxxxxxxxxxxxxxxxxx > 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.klaver@xxxxxxxxxxx
