On 11/25/20 8:43 AM, ha...@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.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.kla...@aklaver.com>
Sent: Wednesday, November 25, 2020 9:07 AM
To: Hagen Finley <ha...@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 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.kla...@aklaver.com
