Re: Query is fast and function is slow

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(dot)pundt(at)rp-online(dot)de> ---- http://rp-online.de/ ----