From: Thomas Pundt <mlists@rp-online.de>
Reply-To: Thomas Pundt <thomas.pundt@rp-online.de>
Organization: RP Online GmbH
To: pgsql-sql@postgresql.org,
 Richard Ray <rray@mstc.state.ms.us>
Subject: Re: Query is fast and function is slow
Date: Thu, 7 Dec 2006 16:41:27 +0100
User-Agent: KMail/1.9.4
References: <Pine.LNX.4.64.0612060934410.15200@rray.drdc.mstc.ms.gov>
	<200612071053.01287.mlists@rp-online.de>
	<Pine.LNX.4.64.0612070826040.16909@rray.drdc.mstc.ms.gov>
In-Reply-To: <Pine.LNX.4.64.0612070826040.16909@rray.drdc.mstc.ms.gov>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200612071641.27686.mlists@rp-online.de>

Hi,

On Thursday 07 December 2006 15:53, Richard Ray wrote:
| But this same statement in a function takes several minutes;
|
| My SQL knowledge is pitiful so would you explain how to use
| "explain analyze" in the function
|
| I get errors when I try to load the file with
| raise notice  ''%'',explain analyze select doc_num from documents where
| doc_num = doc_number;
|
| dcc=# \i
| /src/check_for_update_permission
| psql:/src/check_for_update_permission:52:
| ERROR:  syntax error at or near "analyze" at character 16
| QUERY:  SELECT explain analyze select doc_num from documents where doc_num
| =  $1
| CONTEXT:  SQL statement in PL/PgSQL function "check_for_update_permission"
| near line 18
| psql:/src/check_for_update_permission:52:
| LINE 1: SELECT explain analyze select doc_num from documents where d...
| psql:/src/check_for_update_permission:52:
| ^
| dcc=#

ok, seems you can't use a SQL statement as expression here; instead try
using a "for statement" then:

  for v_rec in explain analyze <your_query_here>
  loop
      raise notice '%', v_rec;
  end loop;

don't forget to declare "v_rec text;"

Ciao,
Thomas

-- 
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----