1

I am trying to write a For loop like this:

DO
$$
DECLARE
rec TEXT  ;
rec1 record  ;
BEGIN
FOR rec IN 
select my_schema_name from public.table1
loop 
PERFORM set_config('search_path', rec,false);
execute 'select *  from table2 where status  = 'NEW' into rec1' ;     
RAISE NOTICE 'Record is : %', rec1;
END LOOP;    
END;
$$
LANGUAGE plpgsql;

But I am getting the Syntax error :

ERROR: syntax error at or near "' into rec1'"

Can some one help me with the correct syntax of writing a Select statement having a WHERE clause with execute in the For loop.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
ptilloo
  • 55
  • 1
  • 8

1 Answers1

0

You have to double quote your string in the where clause, e.g.

Data Sample

CREATE TABLE t (id int, status text);
INSERT INTO t VALUES (42,'bar');

Anonymous code block

DO $$
DECLARE
rec1 record  ;
BEGIN
EXECUTE 'SELECT * FROM t WHERE status=''bar''' INTO rec1;     
RAISE NOTICE 'The answer is %', rec1.id;
END; $$ LANGUAGE plpgsql;

NOTICE:  The answer is 42
Jim Jones
  • 18,404
  • 3
  • 35
  • 44