Trouble using WITH on INSERT ("missing FROM-clause entry")

Question

I'm trying to build a new row with information from some previous CTE's but I can't seem to get the syntax right.

BEGIN;
UPDATE users
SET balance = balance - 10
WHERE user_id = 1;

WITH te AS (
  SELECT accountInsertOrSelect('the.hound', 'farcebook', 'rong') AS account_id
), tr AS (
  SELECT account_id FROM accounts
  WHERE user_id = 1
  AND provider = 'farcebook'
  LIMIT 1
)
INSERT INTO tips (tipper_id, tippee_id, amount)
VALUES (te.account_id, tr.account_id, 10);
COMMIT;

Gives the error missing FROM-clause entry for table "te"

Answer 1

You need to specify the CTE in the FROM CLAUSE when you try to insert, like so:

INSERT INTO tips (tipper_id, tippee_id, amount) VALUES
((SELECT account_id FROM te), (SELECT account_id from tr), 10)

Answer 2

INSERT INTO tips (tipper_id, tippee_id, amount)
select 
    (select account_id from te), 
    (select account_id from tr), 
    10

or

INSERT INTO tips (tipper_id, tippee_id, amount)
select te.account_id, tr.account_id, 10
from te cross join tr