How do I call a declared array variable in a where clause in postgres?

Question

I am trying to build a declared array from all the dogs that share the same family_id and query the dog_characteristics table using the array.

CREATE OR REPLACE FUNCTION update_dog_characteristics_guarantor_id()
  RETURNS trigger AS $$
  DECLARE dog_ids INT[];

  BEGIN
    SELECT id into dog_ids FROM dogs WHERE dogs.family_id = OLD.id;
    IF ((OLD.family_id IS NOT NULL) && ((SELECT COUNT(*) FROM dog_ids) > 0)) THEN
      UPDATE
        dog_characteristics
      SET
        guarantor_id = NEW.guarantor_id
      WHERE
        dog_characteristics.account_id = OLD.account_id
        AND dog_characteristics.dog_id IN ANY(dog_ids);
      RETURN NULL;
    END IF;
    RETURN NULL;
  END;
$$ LANGUAGE plpgsql;

What I have tried

 AND dog_characteristics.dog_id = ANY(dog_ids);
 AND dog_characteristics.dog_id = ANY(dog_ids::int[]);
 AND dog_characteristics.dog_id IN (dog_ids::int[]);
 AND dog_characteristics.dog_id IN (dog_ids);
 AND dog_characteristics.dog_id IN (ARRAY(dog_ids));
 AND dog_characteristics.dog_id IN ARRAY(dog_ids);
 AND dog_characteristics.dog_id IN implode( ',', dog_ids);

Most common error

  ERROR:  malformed array literal: "672"
  DETAIL:  Array value must start with "{" or dimension information.
  CONTEXT:  PL/pgSQL function update_dog_characteristics_guarantor_id() line 5 at SQL statement

That gives me the error about the malformed array literal. Is it because it only returns one id? — Peter Black
– Peter Black, Commented Nov 28, 2017 at 22:04
I tried <code> = dog_ids </code> and that fails with the same error. — Peter Black
– Peter Black, Commented Nov 28, 2017 at 22:06

user330315 · Accepted Answer · 2017-11-28 22:05:12Z

9

There are multiple errors in your trigger function.

As dog_ids is declared as an array, the result of the first select has to be an array as well. To do that, you need to aggregate all IDs that are returned from the query.

So the first select statement should be

 select array_agg(id) --<< aggregate all IDs into an array
   into dog_ids 
 FROM dogs 
 WHERE dogs.family_id = OLD.id;

To check if an array has elements, you can't use select count(*), you need to use use array_length() or cardinality().

The && is not the "AND" operator in SQL - that's AND - so the if should be:

IF OLD.family_id IS NOT NULL AND cardinality(dog_ids) > 0 THEN
  ... 
END IF;

The where condition on the array should be:

AND dog_characteristics.dog_id = ANY(dog_ids);

answered Nov 28, 2017 at 22:05

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I call a declared array variable in a where clause in postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related