CODEWITHSUNDEEP
arrayspostgresqlplpgsql
Peter Black
Peter Black

Reputation: 1172

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

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

Upvotes: 4

Views: 7867

Answers (1)

user330315
user330315

Reputation:

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);

Upvotes: 9

Related Questions