title: Error is not a known variable while assigning a query with a single row result
body: <p>I have three tables: prescriptions, items, and orders.
  The prescriptions and items table both have a non nullable column called price.
  In the orders table, I have multiple columns.</p>
  <ol>
  <li>type - enum of either 'prescription' or 'item'</li>
  <li>amount - non nullable int with default of 1</li>
  <li>price</li>
  </ol>
  <p>I'm trying to create trigger that checks whether the order type is prescription or order, and inserts the price multiplied by the amount into the orders table. I'm also throwing an error if the price is manually inserted, because I only want it to be calculated on the server side.</p>
  <p>My code so far is this.</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE OR REPLACE FUNCTION trg_orders_check()
    RETURNS trigger
    LANGUAGE plpgsql AS
  $func$
  BEGIN
    IF NEW.type = 'prescription' AND NEW.prescription_id IS NULL THEN
      RAISE EXCEPTION 'Must include prescription_id';
    ELSIF NEW.TYPE = 'prescription' THEN
      base_price := SELECT price FROM prescriptions
      WHERE prescription_id = NEW.prescription_id;
      INSERT INTO NEW (price) VALUES (base_price * NEW.amount);
    ELSIF NEW.TYPE = 'item' AND NEW.item_id IS NULL THEN
      RAISE EXCEPTION 'Must include item_id';
    ELSIF NEW.TYPE = 'prescription' THEN
      base_price float := SELECT price FROM orders
      WHERE item_id = NEW.item_id;
      INSERT INTO NEW (price) VALUES (base_price * NEW.amount);
    ELSE
      RAISE EXCEPTION 'Type must be either prescription or item';
    END IF;
  END
  $func$;
  </code></pre>
  <p>Right now I'm getting the error
  <code>&quot;base_price&quot; is not a known variable</code></p>
  <p>I get the same error if I do <code>base_price float := ...expression</code></p>
  <p>I'm wondering what is causing that error, and more broadly if this is the correct approach for creating the trigger I outlined above. Any help would be appreciated.</p>