0

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?

0 Answers0