Intersection of multiple text arrays: ERROR: array value must start with "{"

Question

I'm attemping to get the functions in this question to work: Intersection of multiple arrays in PostgreSQL

Unlike that question, I want to intersect text arrays instead of integer arrays. I've modified both functions accordingly. Base array intersect function:

CREATE FUNCTION array_intersect(a1 text[], a2 text[]) RETURNS text[] AS $$
DECLARE
    ret text[];
BEGIN
    IF a1 is null THEN
        return a2;
    ELSEIF a2 is null THEN
        RETURN a1;
    END IF;
    SELECT array_agg(e) INTO ret
    FROM (
        SELECT unnest(a1)
        INTERSECT
        SELECT unnest(a2)
    ) AS dt(e);
    RETURN ret;
END;
$$ language plpgsql;

Aggregate function definition:

CREATE AGGREGATE utility.array_intersect_agg(
    sfunc    = array_intersect,
    basetype = text[],
    stype    = text[],
    initcond = NULL
);

I get the error "ERROR: array value must start with "{" or dimension information SQL state: 22P02" when I try to run the following code:

SELECT array_intersect_agg(test)
  FROM(
    SELECT ARRAY['A','B','C'] test
    UNION ALL
    SELECT ARRAY['A','C'] test
    ) a

What needs to change in order for these functions to work?

klin · Accepted Answer · 2015-12-08 21:39:33Z

1

For the documentation:

initial_condition

The initial setting for the state value. This must be a string constant in the form accepted for the data type state_data_type. If not specified, the state value starts out null.

So the aggregate declaration should look like this:

CREATE AGGREGATE array_intersect_agg(
    sfunc    = array_intersect,
    basetype = text[],
    stype    = text[]
);

answered Dec 8, 2015 at 21:39

klin

123k15 gold badges241 silver badges263 bronze badges

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

Collectives™ on Stack Overflow

Intersection of multiple text arrays: ERROR: array value must start with "{"

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related