-1

I found a previous post (Cursors in BigQuery) where there exists a way to declare cursors on bigquery. This works well and good when the cursor subquery is not present as a parameter.

Currently I was going through FOR..IN EXECUTE construct in Netezza. It behaves extactly like a cursor construct except here, the sql present is a dynamic sql. This dynamic sql is first executed, after that the construct boils down to a simple cursor statement.

Consider the following use case, where the sub-query is present as a parameter.

CREATE or replace PROCEDURE  myproc(varchar(256))
    RETURNS INT4
    LANGUAGE NZPLSQL
AS 
BEGIN_PROC 
 declare 
  sqlstr alias for $1;              ---- sqlStr is a parameter
  r1 record;
 begin 
  FOR r1 IN EXECUTE sqlstr         ---- sqlStr is evaluated after that it boils down to cursor statement.
  loop 
   insert into t1 values r1.c1;
  end loop;
 end;
END_PROC@

Is there a similiar way to declare cursors with subquery as a parameter on BigQuery ?

Abhishek Dasgupta
  • 578
  • 1
  • 8
  • 20

1 Answers1

1

Formally, both LOOPs and EXECUTE IMMEDIATE are available in BigQuery and well documented.
Practically - using LOOP to mimic cursor for real table is extremely ineffective and quite a no-no in BigQuery unless you have use-case where this is the only way to go
But it is almost in 100% cases you can express your logic to be done in batch way (as opposed to using cursor)

At the same time if Table is not that big (like lookup tab le for example) - you can select table rows into array and than loop through arrays elements running execute immediate getting the result and inserting it into target table

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • I am also doing the same thing you mentioned in the second paragraph. The problem I am encountering is how to store the result of `EXECUTE IMMEDIATE`. I am currently trying the `INTO` clause but having a hard how to store the complete result set of the query using an `ARRRAY` data structure. Can explain how to store the result of `EXECUTE IMMEDIATE` using the `INTO` clause ? – Abhishek Dasgupta Jan 13 '21 at 05:13
  • Your question quite generic - thus the answer. and in my mind it answers exactly the question you asked! Post new question with specific details, example of what exactly your use case and what the problem you have - and you will get specific answer. Meantime, consider voting up the answer if it was helpful – Mikhail Berlyant Jan 13 '21 at 06:33