FromSql for a type Record

[mersinvald]

Hello, we have a stored procedure in our database that returns a single row with a Record of format (text, text).

I can't figure out what FromSql conversion is suitable in this case: apparently, there is no FromSql for tuples, Vec conversion leads to an error too.

How can I deserialize a Record of such kind?

Thanks!

[sfackler]

It seems reasonable to implement conversions between Records and tuples - I'll poke around tomorrow.

[sfackler]

Could you give a minimal procedure definition that behaves like the one you're using?

[mersinvald]

Thanks!

Sure, here is a definition from the pgAdmin, if you need an SQL to create this procedure, I'll ask my colleagues during the day.

BEGIN
  RETURN QUERY
    SELECT
        t.id
       ,t.body
      FROM table t
END;

Return type: TABLE(id text, body text)

I have thrown away some clauses, but it should output a similar record.

[sfackler]

Ok, so, the easy way that will work right now is to change your stored procedure to return a named composite type:

CREATE TYPE foo AS (
    id INT,
    body TEXT
);

CREATE FUNCTION bar() RETURNS foo
    AS $$ SELECT 1, 'foo' $$
    LANGUAGE SQL;

Then you can use https://github.com/sfackler/rust-postgres-derive on the Rust side to convert the response type.

Adding support for anonymous records will be a bit interesting. The OID of each type in a record is returned from the server, but not at the normal stage we're expecting - it's not part of the type itself, but of each value. Because of how this library works internally, this means we can make things work for records that contain built in Postgres types, but not for records that contain types defined by users or extensions. I don't really know if that limitation can be removed in a reasonable way, unfortunately.

[nerdrew]

Any more thoughts on this?

I think FromSql for a record might simplify my code too. I'm in the process of writing a FromRow derive, but it looks like adding (...) to my selects and returnings, creates a record. I'd like to be able to do this:

#[derive(FromSql)]
pub struct MyStruct {
    pub id: i32,
    pub name: String,
    pub created_at: NaiveDateTime,
    pub updated_at: NaiveDateTime,
}

let my_struct: MyStruct = db_connection
    .query("select (id, name, created_at, updated_at) from my_structs where id = 1", &[])
    .unwrap()
    .iter()
    .next()
    .unwrap()
    .get(0);

[andreytkachenko]

How is it going on this issue. I tried

select (id, name, created_at, updated_at) from my_structs where id = 1

and have no idea how to de-serialize it into tuple. I am using tokio-postgres and https://github.com/sfackler/rust-postgres-derive does not works (it relates to postgres crate). I tried to write own struct and implement FromSql to it but faced with problem:

...
Inner::Record => &Kind::Pseudo,
...

and

...
Inner::RecordArray => &Kind::Pseudo,
...

I thought they should be Kind::Composite(fields) and Kind::Array(Kind::Composite(fields)). I guess it is not implemented?

[sfackler]

Postgres does not consider Records as composite types: https://github.com/sfackler/rust-postgres/blob/master/codegen/src/pg_type.h#L679-L682

[andreytkachenko]

I was confused by this doc https://www.postgresql.org/docs/11/rowtypes.html. Anyway how can I turn above result back into rust's tuple? Can you advise any example?

[sfackler]

You can return a named composite type: #310 (comment)

[njaard]

An easier way to reproduce is select ('foo', 'bar')

[Nemo157]

It would be very useful to have ToSql as well to support writing queries involving record parameters.