From carla.mello@widesoft.com.br Tue Jan  27 15:24:47 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 1D820D1D66C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Jan 2004 15:24:47 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 09112-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Jan 2004 11:24:19 -0400 (AST)
Received: from hidrogenio.widesoft.com.br (hidrogeniov5.widesoft.com.br
	[200.246.206.17])
	by svr1.postgresql.org (Postfix) with ESMTP id 56043D1D6B9
	for <pgsql-sql@postgresql.org>; Tue, 27 Jan 2004 11:24:14 -0400 (AST)
Received: from wsbd01 (startx.widesoft.com.br [200.246.206.56])
	by hidrogenio.widesoft.com.br (Postfix) with SMTP id 1C4D035C9AE
	for <pgsql-sql@postgresql.org>; Tue, 27 Jan 2004 13:24:02 -0200 (BRDT)
Message-ID: <013d01c3e4e9$dbe16300$16011aac@widesoft.com.br>
From: "Carla Mello" <carla.mello@widesoft.com.br>
To: <pgsql-sql@postgresql.org>
Subject: Executing dynamic queries (EXECUTE)
Date: Tue, 27 Jan 2004 13:25:56 -0200
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_013A_01C3E4D9.181D10A0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.7 tagged_above=0.0 required=5.0 tests=HTML_40_50
X-Spam-Level: 
X-Archive-Number: 200402/7
X-Sequence-Number: 16698

This is a multi-part message in MIME format.

------=_NextPart_000_013A_01C3E4D9.181D10A0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

  Hello!

  I need to execute a dynamic query and capture your result in a integer va=
riable.

  I=B4m using the statement "EXECUTE string", but I don=B4t obtain to captu=
re the result of dynamic query.

  See the example:

  =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D
  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:=3D 0;
     for r in select * from pg_tables loop
        v_count:=3D 0;
        v_query :=3D ''select count(*) from '' || r.tablename;
        v_count:=3D EXECUTE v_query;
        if v_count =3D 0 then
           RAISE NOTICE ''Empty table % '',r.tablename;
        end if;
        v_tot:=3D v_tot + 1;
     end loop;
     return v_tot;
  end;
  '
  language 'plpgsql';

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

  =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D
  Somebody could help me?
  Thanks, Carla Mello.

------=_NextPart_000_013A_01C3E4D9.181D10A0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2600.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LE=
FT: #000000 2px solid; MARGIN-RIGHT: 0px">
  <DIV style=3D"FONT: 10pt arial">Hello!</DIV>
  <DIV style=3D"FONT: 10pt arial">&nbsp;</DIV>
  <DIV style=3D"FONT: 10pt arial">I need to execute a dynamic query and cap=
ture=20
  your result in a integer variable.</DIV>
  <DIV style=3D"FONT: 10pt arial">&nbsp;</DIV>
  <DIV style=3D"FONT: 10pt arial">I=B4m using the statement "EXECUTE string=
", but I=20
  don=B4t obtain to capture the result of dynamic query.</DIV>
  <DIV style=3D"FONT: 10pt arial">&nbsp;</DIV>
  <DIV style=3D"FONT: 10pt arial">See the example:</DIV>
  <DIV style=3D"FONT: 10pt arial">&nbsp;</DIV>
  <DIV=20
  style=3D"FONT: 10pt arial">=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</DIV>
  <DIV style=3D"FONT: 10pt arial">create or replace function f_population_c=
heck()=20
  returns bigint as<BR>'<BR>declare<BR>&nbsp;&nbsp; v_tot=20
  bigint;<BR>&nbsp;&nbsp; v_query varchar(4000);<BR>&nbsp;&nbsp; v_count=20
  integer;<BR>&nbsp;&nbsp; r record;<BR>begin<BR>&nbsp;&nbsp; v_tot:=3D=20
  0;<BR>&nbsp;&nbsp; for r in select * from pg_tables=20
  loop<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; v_count:=3D 0;</DIV>
  <DIV style=3D"FONT: 10pt arial">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; v_query :=
=3D=20
  ''select count(*) from '' || r.tablename;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;=20
  v_count:=3D EXECUTE v_query;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if v_count=
 =3D 0=20
  then<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RAISE NOTICE ''E=
mpty=20
  table % '',r.tablename;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; end=20
  if;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; v_tot:=3D v_tot + 1;<BR>&nbsp;&nbsp=
; end=20
  loop;<BR>&nbsp;&nbsp; return v_tot;<BR>end;<BR>'<BR>language 'plpgsql';<B=
R>
  <DIV=20
  style=3D"FONT: 10pt arial">=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</DIV></DIV>
  <DIV style=3D"FONT: 10pt arial">ERROR:&nbsp; parse error at or near "$1" =
at=20
  character 18<BR>CONTEXT:&nbsp; PL/pgSQL function "f_population_check" lin=
e 11=20
  at assignment<BR>
  <DIV=20
  style=3D"FONT: 10pt arial">=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</DIV></DIV>
  <DIV style=3D"FONT: 10pt arial">
  <DIV lang=3Den=20
  style=3D"PADDING-RIGHT: 10px; PADDING-LEFT: 10px; PADDING-BOTTOM: 10px; P=
ADDING-TOP: 10px">Somebody=20
  could help me?</DIV>
  <DIV lang=3Den=20
  style=3D"PADDING-RIGHT: 10px; PADDING-LEFT: 10px; PADDING-BOTTOM: 10px; P=
ADDING-TOP: 10px">Thanks,&nbsp;Carla=20
  Mello.</FONT></DIV></DIV></BLOCKQUOTE></DIV></BODY></HTML>

------=_NextPart_000_013A_01C3E4D9.181D10A0--




From jasiek@klaster.net Sun Feb  1 19:41:21 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id CBFD6D1B53C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun,  1 Feb 2004 19:41:21 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 81506-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun,  1 Feb 2004 15:41:19 -0400 (AST)
Received: from serwer.skawsoft.com.pl (skawina.eu.org [80.48.213.66])
	by svr1.postgresql.org (Postfix) with ESMTP id 1F3BDD1B55C
	for <pgsql-sql@postgresql.org>; Sun,  1 Feb 2004 15:41:18 -0400 (AST)
Received: from klaster.net (core-1.citynet.pl [80.48.135.14])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id A38F22B3E9; Sun,  1 Feb 2004 20:41:17 +0100 (CET)
Message-ID: <401D56E6.7080700@klaster.net>
Date: Sun, 01 Feb 2004 20:43:34 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL;
	rv:1.6b) Gecko/20031208
X-Accept-Language: pl, en-us, en
MIME-Version: 1.0
To: Carla Mello <carla.mello@widesoft.com.br>
Cc: Lista dyskusyjna pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Executing dynamic queries (EXECUTE)
References: <013d01c3e4e9$dbe16300$16011aac@widesoft.com.br>
In-Reply-To: <013d01c3e4e9$dbe16300$16011aac@widesoft.com.br>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/9
X-Sequence-Number: 16700

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



From carla.mello@widesoft.com.br Mon Feb  2 10:35:05 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 52EDFD1CB0F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  2 Feb 2004 10:35:05 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 35916-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  2 Feb 2004 06:35:00 -0400 (AST)
Received: from hidrogenio.widesoft.com.br (hidrogeniov5.widesoft.com.br
	[200.246.206.17])
	by svr1.postgresql.org (Postfix) with ESMTP id 2F65CD1CACD
	for <pgsql-sql@postgresql.org>; Mon,  2 Feb 2004 06:34:57 -0400 (AST)
Received: from wsbd01 (startx.widesoft.com.br [200.246.206.56])
	by hidrogenio.widesoft.com.br (Postfix) with SMTP
	id EE95635D4FE; Mon,  2 Feb 2004 08:34:42 -0200 (BRDT)
Message-ID: <001501c3e978$7fbdd340$16011aac@widesoft.com.br>
From: "Carla Mello" <carla.mello@widesoft.com.br>
To: "Tomasz Myrta" <jasiek@klaster.net>
Cc: "Lista dyskusyjna pgsql-sql" <pgsql-sql@postgresql.org>
References: <013d01c3e4e9$dbe16300$16011aac@widesoft.com.br>
	<401D56E6.7080700@klaster.net>
Subject: Re: Executing dynamic queries (EXECUTE)
Date: Mon, 2 Feb 2004 08:37:03 -0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="ISO-8859-2"
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/38
X-Sequence-Number: 16729

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