I have a recursive query encapsulated inside a function that returns the hierarchical structure of the study:
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 the survey_id to this function ( SELECT * FROM get_survey_results(1) ) and returns all the questions and answers for this survey.
I wanted to define the "level" node in the hierarchy using:
SELECT question_text, array_length(sort_order) AS level, ...etc... FROM get_survey_results(1)
My request returns an error
Array_length (bigint []) function does not exist
The documentation for the PostgreSQL array promises that arrar_length () will handle "anyarray".
What am I doing wrong? Is there an additional package that I need to install?