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 hygiene_112)
>   THEN
>     UPDATE hygiene_119 SET paid = 'SAME';
>   ELSE
>       UPDATE hygiene_119 SET paid = '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