pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedExecuting dynamic queries (EXECUTE)
3+ messages / 2 participants
[nested] [flat]
* Executing dynamic queries (EXECUTE)
@ 2004-01-27 15:25 Carla Mello <carla.mello@widesoft.com.br>
0 siblings, 1 reply; 3+ messages in thread
From: Carla Mello @ 2004-01-27 15:25 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Executing dynamic queries (EXECUTE)
@ 2004-02-01 19:43 Tomasz Myrta <jasiek@klaster.net>
parent: Carla Mello <carla.mello@widesoft.com.br>
0 siblings, 1 reply; 3+ messages in thread
From: Tomasz Myrta @ 2004-02-01 19:43 UTC (permalink / raw)
To: Carla Mello <carla.mello@widesoft.com.br>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Executing dynamic queries (EXECUTE)
@ 2004-02-02 10:37 Carla Mello <carla.mello@widesoft.com.br>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 3+ messages in thread
From: Carla Mello @ 2004-02-02 10:37 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
Hello Tomasz,
I´m going to study the FOR-IN-EXECUTE statement and to try again.
Thanks,
Carla Mello
----- Original Message -----
From: "Tomasz Myrta" <jasiek@klaster.net>
To: "Carla Mello" <carla.mello@widesoft.com.br>
Cc: "Lista dyskusyjna pgsql-sql" <pgsql-sql@postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-02-02 10:37 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-27 15:25 Executing dynamic queries (EXECUTE) Carla Mello <carla.mello@widesoft.com.br>
2004-02-01 19:43 ` Tomasz Myrta <jasiek@klaster.net>
2004-02-02 10:37 ` Carla Mello <carla.mello@widesoft.com.br>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox