Support for postgresql >= 11.1 stored procedures?

1

I wondered (perhaps using QueryBuilder?) if it is currently possible for Diesel to issue:

call my_stored_proc(in_par1, in_par2, out_par1)

where my_stored_proc is created as:

create procedure my_stored_proc(
	name in varchar,
	description in varchar,
	error_msg out varchar)
as
$$
declare
	...
begin
        ...
end;
$$ language pgplsql;

In particular, to be able to bind both in and out parameters, and to be able to collect database exceptions that are RAISE’ed inside the procedure.

It would be great to have this in order to leverage both the power of stored procedures, while still having the flexibility of Diesel’s ORM.

2

We don’t currently have any built-in support for stored procedures, but you could implement your own QueryFragment for the CALL keyword if you wanted. sql_function! would be fine for stored procedure declaration.

That said, it doesn’t sound like you actually need to be using stored procedures at all. The only difference between a procedure and a function is the ability to manage transaction state. If you’re not using BEGIN/COMMIT/ROLLBACK in the body, there’s no reason it can’t be done with a function returning void

3

Hi Sean,

Actually, it turns out that the following code works. Binding inout parameters even works, and the raised exception makes it into Diesel’s DatabaseError. I’m less familiar with PostgreSQL transactions but I may in the future need to write procedures that have a COMMIT or ROLLBACK statement.

Regarding using a sql_function!, is it possible to call it without any associated table, i.e. like

select my_function();

Thanks again!

Here is the code.

    pub fn create(conn: &PgConnection, params: Graph, uuid: Uuid) -> Result<Uuid, ServiceError> {
        let mut out_uuid = Uuid::new_v4();
        let res = diesel::sql_query("call create_proc($1, $2, $3)")
            .bind::<sql_types::Text, _>(params.name)
            .bind::<sql_types::Text, _>(params.desc)
            .bind::<sql_types::Uuid, _>(uuid)
            .bind::<sql_types::Uuid, _>(&out_uuid)
            .execute(conn);
        match res {
            Ok(_) => Ok(true),
            Err(e) => Err(e.into())
        }
    }

where:

create or replace procedure create_proc(
        name in varchar,
        description in varchar,
        uuid in uuid,
        out_uuid inout uuid)
as
$$
declare
        my_id integer;
begin
        select id into my_id from parent_table where uuid = uuid;
        if not found then
                raise exception 'No Source Record for %', uuid;
        end if;

        insert into target_table (name, description, my_id)
        values (name, description, id)
        returning uuid into out_uuid;
end;
$$ language plpgsql;
4

Yes, see http://docs.diesel.rs/diesel/fn.select.html

5

That’s great, thank you again. I do have a related follow up question - I’ll post it in a separate question.
Maybe it’s possible with Diesel as well.

Cheers