Fixing error "function array_length(bigint[]) does not exist"

Question

I have a recursive query, encapsulated within a function, that returns a hierarchical survey "structure":

CREATE OR REPLACE FUNCTION get_survey_results(IN v_survey_id integer DEFAULT 1, IN v_survey_session_id integer DEFAULT NULL)
  RETURNS TABLE(sort_order bigint[], survey_question_id integer, question_text text, required_ind smallint, survey_session_id integer, custom_text text, option_value integer, survey_scale_id integer, survey_scale_type_id integer, parent_survey_question_id integer, parent_survey_scale_option_id integer) AS
$BODY$
BEGIN
    RETURN QUERY
        WITH RECURSIVE Survey_Structure (PATH, survey_question_id, question_text,
            required_ind, survey_session_id, custom_text,
            option_value, survey_scale_id, survey_scale_type_id,
            parent_survey_question_id, parent_survey_scale_option_id, CYCLE)
        AS (
            SELECT ARRAY[row_number() OVER (ORDER BY Survey_Question.sort_order)], Survey_Question.survey_question_id, Survey_Question.question_text, 
                Survey_Question.required_ind, Survey_Response.survey_session_id, Survey_Response.custom_text,
                Survey_Scale_Option.option_value, Survey_Question.survey_scale_id, Survey_Scale.survey_scale_type_id,
                0 as parent_survey_question_id, 0 AS parent_survey_scale_option_id, FALSE
            FROM ...etc...

I can pass a survey_id to this function (SELECT * FROM get_survey_results(1)) and it returns all the questions and answers for that survey.

I wanted to determine the "level" of a node within the hierarchy by using:

SELECT question_text, array_length(sort_order) AS level,
    ...etc...
FROM get_survey_results(1)

My query returns the error

function array_length(bigint[]) does not exist

The PostgreSQL array documentation promises that arrar_length() will handle "anyarray".

What am I doing wrong? Is there an optional package I need to install?

Depending on your system, there's also a chance you need array_upper (that worked for me). groups.google.com/a/greenplum.org/forum/#!topic/gpdb-users/… — combinatorist
– combinatorist, Commented Dec 3, 2019 at 18:57

Jeromy French · Accepted Answer · 2014-01-16 21:34:46Z

58

Closer examination of the documentation shows that array_length() takes two parameters.

I just had to change

SELECT question_text, array_length(sort_order) AS level, ...

to

SELECT question_text, array_length(sort_order, 1) AS level, ...

answered Jan 16, 2014 at 21:34

Jeromy French

12.1k19 gold badges79 silver badges136 bronze badges

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

2 Comments

Craig Ringer Over a year ago

There's discussion of adding the shorthand version on the mailing list at the moment. Feel free to weigh in.

Vasilii Suricov Over a year ago

what could be simpler than to give here the explanations that you managed to find ?! array_length(anyarray, int) returns int returns the length of the requested array dimension. example array_length(array[1,2,3], 1) expect 3

Collectives™ on Stack Overflow

Fixing error "function array_length(bigint[]) does not exist"

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related