Not working on array of array of text.. TEXT[][]

[gabrieligbastos]

I have a function like:

create function check_parameter_2(p_payload text[])
    returns TABLE(id uuid, name text, value text)
    language plpgsql
as
$$
    DECLARE
    BEGIN
        -- Validate and insert payload
        FOR i IN 1..COALESCE(array_length(p_payload, 1), 0) LOOP
            DECLARE
                v_graph_node_field_id UUID;
                v_graph_node_field_name TEXT;
                v_command_field_value TEXT;
            BEGIN
                v_graph_node_field_name := p_payload[i][1]::TEXT;
                v_command_field_value := p_payload[i][2]::TEXT;

                SELECT gnf.id
                INTO v_graph_node_field_id
                FROM saga.graph_node_field gnf
                WHERE gnf.graph_node_id = 'c4ea80a0-cf9a-448a-99b1-e337d9daf053' AND gnf.name = v_graph_node_field_name;

                -- Return the result
                RETURN QUERY SELECT
                    v_graph_node_field_id,
                    v_graph_node_field_name,
                    v_command_field_value;
            END;
        END LOOP;
    END;
    $$;

I tried a lot of different things to make the rust call it and it works. The final workaround was to create a wrapper like:

CREATE OR REPLACE FUNCTION public.check_parameter_6(p_payload TEXT)
    RETURNS TABLE (
      id UUID,
      name TEXT,
      value TEXT
    ) AS $$
    DECLARE
    BEGIN
        RETURN QUERY SELECT * FROM public.check_parameter_2(p_payload::TEXT[]);
    END;
    $$ LANGUAGE plpgsql;

Any solution to make it able to call directly? Some of things i tried, first i created a own class extending HashMap

#[derive(Debug, Serialize, Deserialize)]
pub struct DatabasePayload(pub HashMap<String, String>);

Then i implemented ToSql in it, trying some options, the first was:

impl ToSql for DatabasePayload {
    fn to_sql(&self, ty: &Type, out: &mut BytesMut, ) -> Result<IsNull, Box<dyn Error + Sync + Send>> {
        // Convert the HashMap to the required format: "{{\"uuid_key\",\"value\"}, .. , {\"uuid_key\",\"value\"}}"
        let mut array_elements = Vec::new();
        for (key, value) in &self.0 {
            array_elements.push(format!("{{\"{}\",\"{}\"}}", key, value));
        }
        let array_string = format!("{{{}}}", array_elements.join(","));
        info!("Sending string as: {}", array_string);
        out.extend_from_slice(array_string.as_bytes());
        Ok(IsNull::No)

    fn accepts(ty: &Type) -> bool {
        info!("Asking for: {}", ty.name());
        matches!(ty.name().to_lowercase().as_str(), "text[]" | "_text" | "text")
    }

    to_sql_checked!();
}

This it was saying the max lenght (293838793) of array was higher than the (6) allowed. Then i tried, using this repo as base, to implement a custom array writer

let member_type = match *ty.kind() {
            Kind::Array(ref member) => member,
            _ => return Err("expected array type".into()),
        };

        // let elements: Vec<(String, String)> = self.0.iter()
        //     .map(|(key, value)| (key, value))
        //     // .map(|(key, value)| format!("{{\"{}\",\"{}\"}}", escape_string(key), escape_string(value)))
        //     .collect();

        let dimension = types::ArrayDimension {
            len: self.0.len() as i32,
            lower_bound: 1,
        };
        let inner_dimension = types::ArrayDimension {
            len: 2,
            lower_bound: 1,
        };

        own_array_to_sql(
            Some(dimension),
            member_type.oid(),
            self.0.iter(),
            |e, inner_out| {
                let key = escape_string(e.0);
                let value = escape_string(e.1);
                let formatted_item = format!("{{{},{}}}", key, value);
                let bytes = formatted_item.as_bytes();

                // Log the formatted item and its byte representation
                info!("Formatted item: {}", formatted_item);
                info!("Bytes being sent: {:?}", bytes);

                inner_out.extend_from_slice(bytes);

                info!("Sending key: {} and value: {}", e.0, e.1);
                Ok(IsNull::No)
            },
            out,
        )?;

        Ok(IsNull::No)

something like this, but it was either saying that im trying to send array of type that was not expected, or it sends but getting all null, because it when send it was wrapping my data into quotes, so could never make it work.

i realized from postgres_rust that they are serializing with the string binary protocol. so i changed to:

async fn check_params(&self, payload: DatabasePayload) -> Result<(),DatabaseServiceError> {
        let mut client = self.pool.get().await.map_err(|err| match self.extract_from_pool_error(err) {
            Some(e) => DatabaseServiceError::from(e),
            _ => DatabaseServiceError::Unknown
        })?;

        let inner_array: Vec<_> = payload.0.iter().map(|item| {
            let data = vec![ item.0.to_string(), item.1.to_string() ];
            let dimensions = vec! [ postgres_array::Dimension { len: 2, lower_bound: 1}];
            info!("We have data.len = {} and dimension.fold = {}",  data.len() as i32, dimensions.iter().fold(1, |acc, i| acc * i.len));
            postgres_array::Array::from_parts(data, dimensions)
            // format!("{{{}:{}}}", item.0, item.1)
        }).collect();

        let data = inner_array;
        let dimensions = vec! [ postgres_array::Dimension { len: payload.0.len() as i32, lower_bound: 1}];
        info!("We have data.len = {} and dimension.fold = {}",  data.len() as i32, dimensions.iter().fold(1, |acc, i| acc * i.len));

        let array = postgres_array::Array::from_parts(data, dimensions);

        let params: [&(dyn ToSql + Sync); 1] = [
            &array,
        ];

        let query = "SELECT * FROM public.check_parameter_2($1::TEXT[]);";
        let statement = client.prepare(query).await.map_err(DatabaseServiceError::from)?;
        let rows = client.query(&statement, &params[..]).await.map_err(DatabaseServiceError::from)?;

        for row in rows {
            info!("Got row: node_id {} with key {} and value {}", row.try_get::<&str, Uuid>("id").unwrap_or_default(), row.try_get::<&str, String>("name").unwrap_or_default(), row.try_get::<&str, String>("value").unwrap_or_default());
        }

        Ok(())
    }

Then i got problem of types, because as postgres, even creating my function as TEXT[][], it creates it as TEXT[], and then even defining on rust TEXT[][], it is calling ToSql as _text. So getting the error:

Error: DriverError("error serializing parameter 0: cannot convert between the Rust type `postgres_array::array::Array<postgres_array::array::Array<alloc::string::String>>` and the Postgres type `_text`")

And then if i try to send

postgres_array::array::Array<alloc::string::String>

it will be send with the old quotes, so wont work too. any help on this so i get rid of the workaround function? thanks

[sfackler]

Postgres does not have a concept of TEXT[][] - TEXT[] is the only array type for TEXT values.

[gabrieligbastos]

ok, but, considering this, how can i send it as text[] only without the quotes?

i mean, if i send via DataGrip:

SELECT * FROM public.check_parameter_2('{{"should_fail_at_6","false"},{"should_fail_at_9","false"},{"should_fail_at_7","false"},{"should_fail_at_8","false"},{"should_fail_at_11","false"},{"should_fail_at_3","true"},{"should_fail_at_2","false"},{"should_fail_at_5","false"},{"should_fail_at_10","false"},{"should_fail_at_4","false"}}'::TEXT[]);

it works. I need to somehow send the string { {..}, {..}, {..} } as string, casting to TEXT[] but everything i try at most send this: { "{...}, {...}, {...}" }

How to avoid this extra inner quotes? Any clue?

[gabrieligbastos]

I used TEXT[][] based on some usages on postgresql doc

From:
https://www.postgresql.org/docs/current/arrays.html

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

[sfackler]

The postgres array type is multidimensional. This test case builds a 2x2 array for example: https://github.com/sfackler/rust-postgres-array/blob/master/src/lib.rs#L98-L106

[sfackler]

From that page:

Arrays of a particular element type are all considered to be of the same type, regardless of size or number of dimensions. So, declaring the array size or number of dimensions in CREATE TABLE is simply documentation; it does not affect run-time behavior.

[gabrieligbastos]

Ohh wow, thanks @sfackler I think now i totally got it..

let mut array = Array::from_vec(vec![], 0);
        // array.wrap(0);
        for item in payload.0.iter() {
            if array.iter().len() == 0 {
                array = Array::from_vec(vec![item.0.to_string(), item.1.to_string()], 1);
                array.wrap(1);
            }
            else {
                array.push(Array::from_vec(vec![item.0.to_string(), item.1.to_string()], 1));
            }
        }

This one is the one working for me, as you mentioned, wrapping the dimensions. My code is considering lower_bound 1 to default on db, so got this little bug that was fixed with lower_bound = 1.

Thanks so much :)