I have a postgres query I am trying to run inside of a bash script. It uses a do loop and a variable and I am having trouble with calling the variable inside the loop. My query is below:
for i in "${account[@]}"
do
var="'$i'"
psql -p5433 mart -X -v v1=$var -v "ON_ERROR_STOP=1" <<_EOF_ > /home/banderson/scripts/log/next_question.log
TRUNCATE TABLE analytics.nq_q_order_2;
DO \$\$
DECLARE questions datawarehouse.question_fact%rowtype;
DECLARE curr_assignment_uid uuid;
DECLARE curr_course_uid uuid;
DECLARE curr_question_id bigint;
DECLARE question_order int;
BEGIN
question_order = 0;
curr_assignment_uid = NULL;
curr_course_uid = NULL;
curr_question_id = NULL;
FOR questions IN
SELECT *
FROM datawarehouse.question_fact qf
JOIN (SELECT *
FROM analytics.next_question_assignments
WHERE account_uid = :v1
ORDER BY assignment_uid) ga
ON ga.account_uid = qf.account_uid AND ga.assignment_uid = qf.assignment_uid AND ga.course_uid = qf.course_classroom_uid
WHERE answered_time IS NOT NULL
ORDER BY qf.account_uid, qf.course_classroom_uid, qf.assignment_uid, answered_time, question_id
LOOP
IF curr_assignment_uid IS NULL THEN
curr_assignment_uid = questions.assignment_uid;
curr_course_uid = questions.course_classroom_uid;
curr_question_id = questions.question_id;
question_order = 1;
ELSIF (questions.assignment_uid = curr_assignment_uid) AND (questions.course_classroom_uid = curr_course_uid) THEN
INSERT INTO analytics.nq_q_order VALUES (questions.account_uid, curr_course_uid, curr_assignment_uid, curr_question_id, question_order);
curr_assignment_uid = questions.assignment_uid;
curr_course_uid = questions.course_classroom_uid;
curr_question_id = questions.question_id;
question_order = question_order + 1;
ELSE
INSERT INTO analytics.nq_q_order VALUES (questions.account_uid, curr_course_uid, curr_assignment_uid, curr_question_id, question_order);
curr_assignment_uid = questions.assignment_uid;
curr_course_uid = questions.course_classroom_uid;
curr_question_id = questions.question_id;
question_order = 1;
END IF;
END LOOP;
INSERT INTO analytics.nq_q_order VALUES (questions.account_uid, curr_course_uid, curr_assignment_uid, curr_question_id, question_order);
END \$\$;
The problem I am having is with this line here:
WHERE account_uid = :v1
I get a syntax error:
ERROR: syntax error at or near ":"
LINE 17: WHERE account_uid = :v1
How can I make this do loop work and accept the variable?