09-ruby-first.sql

-- 09-ruby-first.sql
--
-- Ruby-first style: think of `Postr` as a tiny Ruby database API that lives
-- inside PostgreSQL. Setup, writes, reads, and business logic all stay in Ruby.

CREATE EXTENSION IF NOT EXISTS postr;

DROP FUNCTION IF EXISTS public.ruby_customer_dashboard(text);
DROP TABLE IF EXISTS public.postr_ruby_orders;

DO $ruby$
  Postr.exec(<<~SQL)
    CREATE TABLE IF NOT EXISTS public.postr_ruby_orders(
      id bigserial PRIMARY KEY,
      customer_name text NOT NULL,
      total integer NOT NULL,
      status text NOT NULL
    )
  SQL

  Postr.exec("TRUNCATE public.postr_ruby_orders")

  Postr.transaction do
    [
      ["Ada", 120, "paid"],
      ["Ada", 80, "paid"],
      ["Ada", 45, "draft"],
      ["Grace", 220, "paid"]
    ].each do |customer_name, total, status|
      Postr.exec(
        "INSERT INTO public.postr_ruby_orders(customer_name, total, status) VALUES ($1, $2, $3)",
        customer_name,
        total,
        status
      )
    end
  end

  Postr.notice("seeded postr_ruby_orders from Ruby")
$ruby$ LANGUAGE ruby;

CREATE FUNCTION public.ruby_customer_dashboard(customer_name text)
RETURNS jsonb
LANGUAGE ruby
AS $ruby$
  class CustomerDashboard
    def initialize(customer_name:)
      @customer_name = customer_name
    end

    def call
      {
        "customer_name" => @customer_name,
        "total_orders" => Postr.value(
          "SELECT count(*) FROM public.postr_ruby_orders WHERE customer_name = $1",
          @customer_name
        ),
        "paid_revenue" => Postr.value(
          "SELECT coalesce(sum(total), 0) FROM public.postr_ruby_orders WHERE customer_name = $1 AND status = $2",
          @customer_name,
          "paid"
        ),
        "latest_order" => Postr.first(
          "SELECT id, total, status FROM public.postr_ruby_orders WHERE customer_name = $1 ORDER BY id DESC LIMIT 1",
          @customer_name
        ),
        "paid_orders" => Postr.select(
          "SELECT id, total FROM public.postr_ruby_orders WHERE customer_name = $1 AND status = $2 ORDER BY id",
          @customer_name,
          "paid"
        )
      }
    end
  end

  def call(customer_name:)
    CustomerDashboard.new(customer_name:).call
  end
$ruby$;

SELECT public.ruby_customer_dashboard('Ada');