Made Up of Wires

Simple Timing in plpgsql

June 18, 2025

daybook, sql | permalink

There are numerous ways to get the current date/time in Postgres (see https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT). However, to do some simple timing of operations, you want clock_timestamp(). Here's an example in plpgsql:

do $body$
declare
    op_start time;
    op_end time;
begin
    op_start = (select clock_timestamp());

    for i in 1..1000000 loop
        perform (select 1 + 1);
    end loop;

    op_end = (select clock_timestamp());

    raise info '%', (op_end - op_start);

end;
$body$

Name it "timing.sql" and run with psql -f timing.sql.

Variable Number of SQL Clause Predicates

January 4, 2024

daybook, rust, sql | permalink

From reviewing my notes on sqlx from a couple years ago, it seems I came up with a way to handle a variable number of SQL WHERE clause predicates, since there's no built-in way to do this. Looking at it now, it seems a little convoluted. It seems I also got a couple of suggestions from the maintainer, which I unfortunately only wrote down but didn't actually use, because they would have been a good foundation to start from, and are similar to what I did this time around. Anyway, I'm glad I forgot to check my notes before starting this little endeavor, because I think my old method would have thrown me off track for a bit. This time around, my solution is more robust, capable of handling any number of predicates. Here it is in an API I'm building with Dropshot.

/// Get union membership by union.
#[endpoint {
    method = GET,
    path = "/api/union-members",
}]
async fn get_union_members(
    rqctx: RequestContext<ServerContext>,
    query: Query<UnionMembersQueryArgs>,
) -> Result<HttpResponseOk<Vec<UnionMembers>>, HttpError> {
    let context = rqctx.context();
    let query_args = query.into_inner();

    let mut query: QueryBuilder<Postgres> = QueryBuilder::new(
        "\
        SELECT \
            name, year, members, member_type, eligible_to_vote, source, source_url, notes \
        FROM unions u \
        JOIN union_members um ON u.id = um.union_id\
        ",
    );

    // Add WHERE clause if there are any query parameters.
    let mut predicates = vec![];
    if let Some(v) = query_args.member_type {
        predicates.push((" member_type = ", DbValueTypes::String(v)));
    }
    if let Some(v) = query_args.eligible_to_vote {
        predicates.push((" eligible_to_vote = ", DbValueTypes::Bool(v)));
    }
    if !predicates.is_empty() {
        let mut predicates = predicates.into_iter().peekable();
        query.push(" WHERE ");
        while let Some((text, var)) = predicates.next() {
            query.push(text);
            match var {
                DbValueTypes::Bool(x) => query.push_bind(x),
                DbValueTypes::I32(x) => query.push_bind(x),
                DbValueTypes::String(x) => query.push_bind(x),
            };
            if predicates.peek().is_some() {
                query.push(" AND ");
            }
        }
    }

    // Order results (by number of union members by default).
    query.push(" ORDER BY ");
    let order_clause = match query_args.order_by {
        Some(UnionMemberOrder::Members) | None => "members DESC",
        Some(UnionMemberOrder::Union) => "name ASC",
        Some(UnionMemberOrder::Year) => "year DESC",
    };
    query.push(order_clause);

    let query = query.build_query_as();
    let unions_members = query.fetch_all(&context.pool).await.unwrap();

    Ok(HttpResponseOk(unions_members))
}