postgres.yml

name: PostgreSQL Database Examples
description: Examples for using the db action with PostgreSQL

vars:
  postgres_host: "{{POSTGRES_HOST ?? 'localhost'}}"
  postgres_port: "{{POSTGRES_PORT ?? 5432}}"
  postgres_user: "{{POSTGRES_USER ?? ''}}"
  postgres_pass: "{{POSTGRES_PASS ?? ''}}"
  postgres_database: probe_test

jobs:
- name: Setup Database
  id: setup
  steps:
  - name: Set user when POSTGRES_USER exists
    skipif: vars.postgres_user == ""
    uses: hello
    outputs:
      user: vars.postgres_user
  - name: Get username by whoami when POSTGRES_USER not exists
    skipif: vars.postgres_user != ""
    uses: shell
    with:
      cmd: whoami
    test: res.code == 0
    outputs:
      user: replace(res.stdout, '\n', '')
  - name: Create Test Database
    uses: db
    with:
      dsn: "postgres://{{outputs.user}}:{{vars.postgres_pass}}@{{vars.postgres_host}}:{{vars.postgres_port}}/postgres?sslmode=disable"
      query: "CREATE DATABASE {{vars.postgres_database}}"
    test: res.code == 0

- name: PostgreSQL Basic Operations
  id: basic-ops
  needs: [setup]
  defaults:
    db:
      dsn: "postgres://{{outputs.user}}:{{vars.postgres_pass}}@{{vars.postgres_host}}:{{vars.postgres_port}}/{{vars.postgres_database}}?sslmode=disable"
  steps:
  - name: Test PostgreSQL Connection
    uses: db
    with:
      query: "SELECT 1 as connection_test, NOW() as server_time, version() as pg_version"
    test: res.code == 0
    outputs:
      server_time: res.rows__0__server_time

  - name: Create Test Table
    uses: db
    with:
      query: |
        CREATE TABLE IF NOT EXISTS products (
          id SERIAL PRIMARY KEY,
          name VARCHAR(100) NOT NULL,
          description TEXT,
          category VARCHAR(50),
          price DECIMAL(10,2),
          stock_quantity INTEGER DEFAULT 0,
          is_active BOOLEAN DEFAULT true,
          tags JSONB,
          created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
          updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
        )
    test: res.code == 0

  - name: Insert Sample Products
    uses: db
    with:
      query: |
        INSERT INTO products (name, description, category, price, stock_quantity, tags) 
        VALUES ($1, $2, $3, $4, $5, $6)
      params:
        - "Wireless Headphones"
        - "High-quality wireless headphones with noise cancellation"
        - "Electronics"
        - "199.99"
        - "50"
        - '{"features": ["wireless", "noise-cancellation", "bluetooth"], "brand": "TechCorp"}'
    test: res.code == 0 && res.rows_affected == 1

  - name: Bulk Insert Products
    uses: db
    with:
      query: |
        INSERT INTO products (name, description, category, price, stock_quantity, tags) VALUES 
        ('Laptop Stand', 'Adjustable aluminum laptop stand', 'Accessories', 49.99, 100, '{"material": "aluminum", "adjustable": true}'),
        ('USB-C Cable', 'High-speed USB-C charging cable', 'Cables', 19.99, 200, '{"length": "2m", "speed": "high"}'),
        ('Mechanical Keyboard', 'RGB mechanical gaming keyboard', 'Gaming', 149.99, 25, '{"switches": "cherry-mx", "rgb": true}'),
        ('Webcam HD', '1080p HD webcam for video calls', 'Electronics', 89.99, 75, '{"resolution": "1080p", "autofocus": true}')
    test: res.code == 0 && res.rows_affected == 4

  - name: Query Products with JSON Operations
    uses: db
    with:
      query: |
        SELECT 
          id, 
          name, 
          price, 
          tags->>'brand' as brand,
          tags->'features' as features,
          jsonb_array_length(COALESCE(tags->'features', '[]'::jsonb)) as feature_count
        FROM products 
        WHERE tags ? 'features'
        ORDER BY price DESC
    test: res.code == 0

  - name: Update Product with JSONB
    uses: db
    with:
      query: |
        UPDATE products 
        SET 
          tags = tags || '{"updated": true, "discount": 10}'::jsonb,
          updated_at = NOW()
        WHERE category = $1
      params:
        - "Electronics"
    test: res.code == 0

  - name: Complex Query with Window Functions
    uses: db
    with:
      query: |
        SELECT 
          name,
          category,
          price,
          stock_quantity,
          AVG(price) OVER (PARTITION BY category) as avg_category_price,
          RANK() OVER (PARTITION BY category ORDER BY price DESC) as price_rank,
          CASE 
            WHEN price > AVG(price) OVER () THEN 'Above Average'
            ELSE 'Below Average'
          END as price_category
        FROM products
        ORDER BY category, price DESC
    test: res.code == 0

  - name: Array and Text Search
    uses: db
    with:
      query: |
        SELECT 
          name,
          description,
          ts_rank_cd(to_tsvector('english', name || ' ' || description), query) as relevance
        FROM products, 
             to_tsquery('english', $1) query
        WHERE to_tsvector('english', name || ' ' || description) @@ query
        ORDER BY relevance DESC
      params:
        - "wireless & headphones"
    test: res.code == 0

  # PostgreSQL specific features
  - name: Create and Use Custom Types
    uses: db
    with:
      query: |
        DO $$
        BEGIN
          IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'product_status') THEN
            CREATE TYPE product_status AS ENUM ('active', 'inactive', 'discontinued');
          END IF;
        END$$;
    test: res.code == 0

  - name: Generate Series and Random Data
    uses: db
    with:
      query: |
        SELECT 
          generate_series(1, 5) as id,
          'Product ' || generate_series(1, 5) as name,
          random() * 100 as random_price,
          current_date + (random() * 365)::int as random_date
    test: res.code == 0

  - name: Database Statistics
    uses: db
    with:
      query: |
        SELECT 
          schemaname,
          tablename,
          attname as column_name,
          n_distinct,
          correlation
        FROM pg_stats 
        WHERE tablename = 'products'
        ORDER BY schemaname, tablename, attname
    test: res.code == 0

- name: PostgreSQL Advanced Features
  id: advanced
  needs: [basic-ops]
  steps:
  - name: Show PostgreSQL Version and Settings
    uses: db
    with:
      dsn: "postgres://{{outputs.user}}:{{vars.postgres_pass}}@{{vars.postgres_host}}:{{vars.postgres_port}}/{{vars.postgres_database}}?sslmode=disable"
      query: "SELECT version() as pg_version, current_database() as current_db"
    test: res.code == 0

- name: Cleanup
  id: cleanup
  needs: [advanced]
  steps:
  - name: Drop Test Objects
    uses: db
    with:
      dsn: "postgres://{{outputs.user}}:{{vars.postgres_pass}}@{{vars.postgres_host}}:{{vars.postgres_port}}/{{vars.postgres_database}}?sslmode=disable"
      query: |
        DROP TABLE IF EXISTS products;
        DROP TYPE IF EXISTS product_status;
    test: res.code == 0

  - name: Drop Test Database
    uses: db
    with:
      dsn: "postgres://{{outputs.user}}:{{vars.postgres_pass}}@{{vars.postgres_host}}:{{vars.postgres_port}}/postgres?sslmode=disable"
      query: "DROP DATABASE IF EXISTS {{vars.postgres_database}}"
    test: res.code == 0