[SQL] Executing dynamic queries (EXECUTE)

[Carla Mello]

  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 18CONTEXT:  PL/pgSQL function "f_population_check" line 11 
  at assignment
  ======
  
  Somebody 
  could help me?
  Thanks, Carla 
  Mello.

Re: [SQL] Executing dynamic queries (EXECUTE)

[Carla Mello]

Hello Tomasz,

I´m going to study the FOR-IN-EXECUTE statement and to try again.

Thanks,
Carla Mello

- Original Message -
From: "Tomasz Myrta" <[EMAIL PROTECTED]>
To: "Carla Mello" <[EMAIL PROTECTED]>
Cc: "Lista dyskusyjna pgsql-sql" <[EMAIL PROTECTED]>
Sent: Sunday, February 01, 2004 5:43 PM
Subject: Re: [SQL] Executing dynamic queries (EXECUTE)


> Dnia 2004-01-27 16:25, Użytkownik Carla Mello napisał:
> >
> > 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.
> >   v_count:= EXECUTE v_query;
>
> Documentation:
> 19.5.4. Executing dynamic queries
>
> "The results from SELECT queries are discarded by EXECUTE, and SELECT
> INTO is not currently supported within EXECUTE. So, the only way to
> extract a result from a dynamically-created SELECT is to use the
> FOR-IN-EXECUTE form described later."
>
> Regards,
> Tomasz Myrta


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster