Intersection of array with select statement in postgres

Question

I am trying to do an intersect of a select statement with an array that is dynamically computed in a loop and then measure the time taken for each of those intersect queries.

So in the first iteration of the loop, I want the query to be like this:

'select sub_id from table where main_id=1 INTERSECT select array[''80'']';

In the second iteration:

'select sub_id from table where main_id=1 INTERSECT select array[''80'', ''81'']';

and so on and measure the time taken in each of those iterations to execute the query.

When I run this script, I get the error:

CONTEXT: PL/pgSQL function inline_code_block at assignment

which corresponds to the line where I initialize query1. How do I correct the assignment to the query1? I am using PostgreSQL 9.1.

arr := '{}';
for j in 80..120 loop
  arr := array_append(arr, j::text);
  query1 := 'select sub_id from table where main_id=1 INTERSECT' || ' select unnest(arr)';
  execute 'explain (analyse, format json) ' || query1 into p;
  t := (p->0->>'Planning Time')::float + (p->0->>'Execution Time')::float;
  total_time := total_time + t;
end loop;

That's not the error. THat's debug info. The error is probably on the preceding line in the logs or output. — Chris Travers
– Chris Travers, Commented Oct 3, 2017 at 6:44

Andomar · Accepted Answer · 2017-10-03 07:14:03Z

3

The query text you execute can't reference variables from the calling scope. You have to pass the variable as a parameter with using like:

query1 := 'select sub_id from YourTable where main_id=1 INTERSECT select unnest($1)';
execute 'explain (analyse, format json) ' || query1 using arr into p;
                                                    ^^^^^^^^^

Working example at rextester.com

edited Oct 3, 2017 at 7:14

answered Oct 3, 2017 at 7:08

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Intersection of array with select statement in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related