Executing dynamic queries (EXECUTE) - Mailing list pgsql-sql

Hello!

 

I need to execute a dynamic query and capture your result in a integer variable.

 

I´m using the statement "EXECUTE string", but I don´t obtain to capture the result of dynamic query.

 

See the example:

 

======================================================

create or replace function f_population_check() returns bigint as
'
declare
   v_tot bigint;
   v_query varchar(4000);
   v_count integer;
   r record;
begin
   v_tot:= 0;
   for r in select * from pg_tables loop
      v_count:= 0;

      v_query := ''select count(*) from '' || r.tablename;
      v_count:= EXECUTE v_query;
      if v_count = 0 then
         RAISE NOTICE ''Empty table % '',r.tablename;
      end if;
      v_tot:= v_tot + 1;
   end loop;
   return v_tot;
end;
'
language 'plpgsql';

======================================================

ERROR:  parse error at or near "$1" at character 18
CONTEXT:  PL/pgSQL function "f_population_check" line 11 at assignment

======================================================

Somebody could help me?

Thanks, Carla Mello.