pg_aggregate needs a makeover

[workingjubilee]

Initially inspired from #1357 and its discoveries.

Other issues related to aggregates specifically:

• Allow CREATE OR REPLACE AGGREGATE #1552
• Relax bounds on aggregate functions? #1272
• Returning tuples from Aggs not supported #477

Related to all SQL generation:

• Redo SQL-gen macros to handle things uniformly #1488

[workingjubilee]

effectively blocked on #1661

[workingjubilee]

Lifted from #1731:

The problem isn't with Aggregate, per se, it's with certain types.

These are aggregates that are supposed to manage composite types. But we know composite types are allocated in Postgres, thus are effectively lifetime-bound. But #[pg_aggregate] and Aggregate don't know how to convey the lifetimes through. Thus even annotating it with something like this does not actually fix the problem:

#[pg_aggregate]
impl<'a> Aggregate for SumScritches<'a> {
    type State = i32;
    const INITIAL_CONDITION: Option<&'static str> = Some("0");
    type Args = pgrx::name!(value, pgrx::composite_type!('a, "Dog"));

    fn state(
        current: Self::State,
        arg: Self::Args,
        _fcinfo: pg_sys::FunctionCallInfo,
    ) -> Self::State {
        todo!()
    }
}

Thus in order to make aggregates work for complex cases we must break the world for them as well, making all their functions use the correct, lifetime-bound types. Hopefully this will let us simplify the code expansion for them as well.