Trouble getting set-returning-function to work

[benthillerkus]

I've got a plpgsql function create_user that takes a username and a password and returns the new user.
To use it in SQL I'd call select * from create_user('jeff', 'password');.
How can I call it with Diesel?

I've tried

  sql_query("select * from create_user(?, ?);")
    .bind::<diesel::sql_types::Text, _>(body.name)
    .bind::<diesel::sql_types::Text, _>(private_id.to_string())
    .first(conn)

this will not compile, because of E0275

and

   sql_function! {
     fn create_user(name: Text, password: Text) -> schema::users::SqlType;
   }

   let register_fn = create_user(body.name, private_id.to_string());
   select(register_fn).first::<models::User>(conn)

which compiles, but does not work (the Serialization into a User fails, the DB operation itself works), because it is the equivalent of select create_user('jeff', 'password') (I believe)

---

Full snippet:

let private_id = uuid::Uuid::new_v4();

let result = conn
    .interact(move |conn| {
        sql_query("select * from create_user(?, ?);")
        .bind::<diesel::sql_types::Text, _>(body.name) // body.name is a String
        .bind::<diesel::sql_types::Text, _>(private_id.to_string())
        .first(conn)
        // let register_fn = functions::create_user(body.name, private_id.to_string());
        // select(register_fn).first::<models::User>(conn)
    })
    .await;

And the compile error:

error[E0275]: overflow evaluating the requirement `<query_builder::sql_query::UncheckedBind<query_builder::sql_query::UncheckedBind<SqlQuery, std::string::String, diesel::sql_types::Text>, std::string::String, diesel::sql_types::Text> as LimitDsl>::Output == _`
  --> src\handler.rs:53:14
   |
53 |             .first(conn)
   |              ^^^^^

For more information about this error, try `rustc --explain E0275`.
warning: `krawall_server` (bin "krawall_server") generated 1 warning
error: could not compile `krawall_server` due to previous error; 1 warning emitted

---

src/db/models.rs

use chrono::{DateTime, Utc};
use serde::Serialize;

#[derive(Debug, Serialize, Queryable)]
pub struct User {
    pub public_id: uuid::Uuid,
    #[serde(skip_serializing)]
    pub password_hashed: String,
    pub name: String,
    pub wins: i16,
    pub losses: i16,
    pub last_login: DateTime<Utc>,
}

src/db/schema.rs (auto generated)

table! {
    users (public_id) {
        public_id -> Uuid,
        password_hashed -> Text,
        name -> Text,
        wins -> Int2,
        losses -> Int2,
        last_login -> Timestamptz,
    }
}

migrations/2022-01-08-231217_create_users/up.sql

create extension pgcrypto;

create table users (
  public_id uuid primary key default gen_random_uuid(),
  password_hashed text not null,
  name text not null default 'Sportsfreund',
  wins smallint not null default 0,
  losses smallint not null default 0,
  last_login timestamptz not null default now()
);

create function create_user(in name text, in password text, out users) as $$
  begin
    insert into users (name, password_hashed)
    values (name, crypt(password, gen_salt('bf')))
    returning * into $3;
  end;
$$ language plpgsql security definer set search_path = public, pg_temp;

---

• rustc --V = rustc 1.57.0 (f1edd0429 2021-11-29)
• PostgreSQL 14

---

Cargo.toml

[package]
# ...
edition = 2021

[dependencies]
deadpool-diesel = {version = "0.3.1", features = ["rt_tokio_1", "postgres", "serde"]}
diesel = {version = "1.4.8", features = ["postgres", "uuidv07", "chrono"]}
chrono = {version = "0.4", features = ["serde"]}
uuid = {version = "0.8", features = ["serde", "v4"]}
serde = {version = "1", features = ["derive"]}
# ...

[weiznich]

Please include the following information:

• Your rust and diesel version
• Which feature flags are enabled for diesel
• Which database system do you use?
• The exact error message as emitted by the compiler
• The definition of any type/function/... used in your code snippet, as long as its not from diesel or rust-std lib.

Otherwise any answer can only guess what might be wrong.

[benthillerkus]

Alright, updated the post. Thank you for taking a look

[weiznich]

  sql_query("select * from create_user(?, ?);")
   .bind::<diesel::sql_types::Text, _>(body.name)
   .bind::<diesel::sql_types::Text, _>(private_id.to_string())
   .first(conn)

This fails because .first() expects a query that implements LimitDsl, so that it is able to apply an limit clause. That's not implemented for raw sql queries as applying an limit clause there may be not supported. Use .get_result() in this case instead.

   sql_function! {
    fn create_user(name: Text, password: Text) -> schema::users::SqlType;
  }

  let register_fn = create_user(body.name, private_id.to_string());
  select(register_fn).first::<models::User>(conn)

Unfortunately does sql_function currently not support sql functions that can be used in FROM clauses. We do not have any idea how to implement that yet.
That written there is a hacky workaround here: By defining your sql function as following:

sql_function! {
    #[sql_name = "* FROM create_user"]
    fn create_user(name: Text, password: Text) -> users::SqlType;
}

you can add the missing from clause. Please note that this may result in future breakage as this is just a hacky workaround.

[benthillerkus]

Thank you so much for your detailed reply!