RPC Functions

const posts = await db.rpc('get_user_posts', { user_id: 1, limit: 10 })

CREATE FUNCTION get_user_by_email(email_param TEXT)
RETURNS users AS $
  SELECT * FROM users WHERE email = email_param;
$ LANGUAGE sql;

CREATE FUNCTION get_user_posts(user_id_param INTEGER, limit_count INTEGER DEFAULT 10)
RETURNS SETOF posts AS $
  SELECT * FROM posts 
  WHERE user_id = user_id_param 
  ORDER BY created_at DESC
  LIMIT limit_count;
$ LANGUAGE sql;

CREATE TYPE user_stats AS (
  user_id INTEGER,
  post_count INTEGER,
  comment_count INTEGER,
  total_likes INTEGER
);
 
CREATE FUNCTION get_user_stats(user_id_param INTEGER)
RETURNS user_stats AS $
  SELECT 
    user_id_param,
    (SELECT COUNT(*) FROM posts WHERE user_id = user_id_param),
    (SELECT COUNT(*) FROM comments WHERE user_id = user_id_param),
    (SELECT COALESCE(SUM(likes), 0) FROM posts WHERE user_id = user_id_param)
$ LANGUAGE sql;

CREATE FUNCTION transfer_funds(
  from_account INTEGER,
  to_account INTEGER,
  amount DECIMAL
) RETURNS BOOLEAN AS $
DECLARE
  from_balance DECIMAL;
BEGIN
  -- Check balance
  SELECT balance INTO from_balance FROM accounts WHERE id = from_account;
  
  IF from_balance < amount THEN
    RETURN FALSE;
  END IF;
  
  -- Transfer
  UPDATE accounts SET balance = balance - amount WHERE id = from_account;
  UPDATE accounts SET balance = balance + amount WHERE id = to_account;
  
  RETURN TRUE;
END;
$ LANGUAGE plpgsql;

const user = await db.rpc('get_user_by_email', { 
  email_param: 'alice@example.com' 
})

// Uses default limit of 10
const posts = await db.rpc('get_user_posts', { user_id_param: 1 })
 
// Override default
const posts = await db.rpc('get_user_posts', { 
  user_id_param: 1, 
  limit_count: 50 
})

const stats = await db.rpc('get_user_stats', { user_id_param: 1 })
// Returns: { user_id: 1, post_count: 42, ... }

const posts = await db.rpc('get_user_posts', { user_id_param: 1 })
// Returns: [{ id: 1, title: '...', ... }, { id: 2, ... }]

const success = await db.rpc('transfer_funds', {
  from_account: 1,
  to_account: 2,
  amount: 100.00
})
 
if (success) {
  console.log('Transfer completed')
}

postbase types generate \
  --database myapp \
  --include-functions \
  --output ./src/db/types.ts

export interface Database {
  public: {
    Functions: {
      get_user_posts: {
        Args: {
          user_id_param: number
          limit_count?: number  // Optional (has default)
        }
        Returns: {
          id: number
          user_id: number
          title: string
          content: string
        }[]
      }
      get_user_stats: {
        Args: {
          user_id_param: number
        }
        Returns: {
          user_id: number
          post_count: number
          comment_count: number
          total_likes: number
        }
      }
    }
  }
}

// Fully typed arguments
const posts = await db.rpc('get_user_posts', {
  user_id_param: 1,   // Must be number
  limit_count: 10,    // Optional, must be number if provided
})
 
// Fully typed return
posts.forEach(post => {
  console.log(post.title)  // TypeScript knows this exists
  console.log(post.foo)    // Error: Property 'foo' does not exist
})

CREATE FUNCTION create_order(
  customer_id INTEGER,
  items JSONB
) RETURNS orders AS $
DECLARE
  new_order orders;
  total DECIMAL := 0;
  item JSONB;
BEGIN
  -- Calculate total
  FOR item IN SELECT * FROM jsonb_array_elements(items) LOOP
    total := total + (item->>'quantity')::INTEGER * (item->>'price')::DECIMAL;
  END LOOP;
  
  -- Create order
  INSERT INTO orders (customer_id, total, status)
  VALUES (customer_id, total, 'pending')
  RETURNING * INTO new_order;
  
  -- Create order items
  INSERT INTO order_items (order_id, product_id, quantity, price)
  SELECT 
    new_order.id,
    (item->>'product_id')::INTEGER,
    (item->>'quantity')::INTEGER,
    (item->>'price')::DECIMAL
  FROM jsonb_array_elements(items) AS item;
  
  RETURN new_order;
END;
$ LANGUAGE plpgsql;

const order = await db.rpc('create_order', {
  customer_id: 123,
  items: [
    { product_id: 1, quantity: 2, price: 29.99 },
    { product_id: 2, quantity: 1, price: 49.99 },
  ]
})

CREATE FUNCTION get_dashboard_stats()
RETURNS TABLE (
  total_users INTEGER,
  active_users INTEGER,
  total_orders INTEGER,
  revenue DECIMAL
) AS $
  SELECT 
    (SELECT COUNT(*) FROM users),
    (SELECT COUNT(*) FROM users WHERE last_login > NOW() - INTERVAL '7 days'),
    (SELECT COUNT(*) FROM orders),
    (SELECT COALESCE(SUM(total), 0) FROM orders WHERE status = 'completed')
$ LANGUAGE sql;

const stats = await db.rpc('get_dashboard_stats', {})

CREATE FUNCTION search_products(
  query TEXT,
  category_id INTEGER DEFAULT NULL,
  min_price DECIMAL DEFAULT NULL,
  max_price DECIMAL DEFAULT NULL
) RETURNS SETOF products AS $
  SELECT * FROM products
  WHERE 
    (query IS NULL OR name ILIKE '%' || query || '%' OR description ILIKE '%' || query || '%')
    AND (category_id IS NULL OR products.category_id = search_products.category_id)
    AND (min_price IS NULL OR price >= min_price)
    AND (max_price IS NULL OR price <= max_price)
  ORDER BY 
    CASE WHEN name ILIKE query || '%' THEN 0 ELSE 1 END,
    name
$ LANGUAGE sql;

const products = await db.rpc('search_products', {
  query: 'laptop',
  min_price: 500,
  max_price: 2000
})

try {
  const result = await db.rpc('transfer_funds', {
    from_account: 1,
    to_account: 2,
    amount: 1000000  // More than balance
  })
} catch (error) {
  if (error.code === 'P0001') {
    // Custom RAISE EXCEPTION in function
    console.error('Transfer failed:', error.message)
  }
}