From: Thomas Pundt <mlists@rp-online.de>
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
Message-ID: <200612071641.27686.mlists@rp-online.de> (raw)
In-Reply-To: <Pine.LNX.4.64.0612070826040.16909@rray.drdc.mstc.ms.gov>
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>
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/ ----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mlists@rp-online.de, rray@mstc.state.ms.us
Subject: Re: Query is fast and function is slow
In-Reply-To: <200612071641.27686.mlists@rp-online.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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