Nested Composites Type

[aoyagikouhei]

I tried nested Composites type. I got an error like below

thread 'main' panicked at 'error retrieving column 0: error deserializing column 0: cannot convert between the Rust type `pg::models::test_user_list_output::UserListOutput` and the Postgres type `type_user_list_output`'

Isn't there a way to do something? Or could you support it as a function?

use postgres_types::{FromSql, ToSql};
use serde::{Deserialize, Serialize};

#[derive(Debug, FromSql, ToSql, Serialize, Deserialize)]
#[postgres(name = "type_hobby")]
pub struct Hobby{
    pub id: i64,
    pub name: String,
    pub hobby_kbn: String,
}

use postgres_types::{FromSql, ToSql};
use serde::{Deserialize, Serialize};
use crate::models::test_hobby::Hobby;

#[derive(Debug, FromSql, ToSql, Serialize, Deserialize)]
#[postgres(name = "type_user_list_output")]
pub struct UserListOutput{
    pub user_id: i64,
    pub name: String,
    pub hobby: Hobby,
}

async fn execute(
    client: &tokio_postgres::Client,
) -> Result<Vec<UserListOutput>, Box<dyn std::error::Error>> {
    let parameter = UserListInput {
        id: 999,
        name: "zzz".to_owned(),
    };
    let sql = r#"
        SELECT 
            ROW(t1.*)::type_user_list_output 
        FROM 
            test_list_user(
                p_parameter := $1
            ) AS t1
    "#;
    Ok(client
        .query(sql, &[&parameter])
        //.query("SELECT test_get_list2(p_parameter := $1)", &[&parameter])
        .await?
        .iter()
        .map(|row| row.get(0))
        .collect())
}

DROP TYPE IF EXISTS type_user_list_input CASCADE;
CREATE TYPE type_user_list_input AS (
  id BIGINT
  ,name TEXT
);

DROP TYPE IF EXISTS type_hobby CASCADE;
CREATE TYPE type_hobby AS (
  id BIGINT
  ,name TEXT
  ,hobby_kbn TEXT
);

DROP TYPE IF EXISTS type_user_list_output CASCADE;
CREATE TYPE type_user_list_output AS (
  user_id BIGINT
  ,name TEXT
  ,hobby type_hobby[]
);

CREATE OR REPLACE FUNCTION test_list_user(
  p_parameter type_user_list_input
) RETURNS SETOF type_user_list_output AS $FUNCTION$
DECLARE
  w_hobbies type_hobby[];
BEGIN
  SELECT
    ARRAY_AGG(t1.*)
  INTO
    w_hobbies
  FROM
    (VALUES (1, 'reading', '00101'), (2, 'movie', '00102')) AS t1(id, name, hobby_kbn)
  ;

  RETURN QUERY SELECT
    p_parameter.id
    ,p_parameter.name
    ,w_hobbies
  ;
END;
$FUNCTION$ LANGUAGE plpgsql;

[sfackler]

Your Postgres definition specifies hobby as an array of type_hobby but your Rust definition specifies hobby as a single Hobby.

[aoyagikouhei]

It worked fine. thank you.