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--