CREATE parametric partial INDEX within a function body

From: Reg Me Please <regmeplease(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: CREATE parametric partial INDEX within a function body
Date: 2009-01-19 19:19:06
Message-ID: 200901192019.06778.regmeplease@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all.

I have a maintenance PL/pgSQL function that needs to recreate a partial index
(not a REINDEX, though).
In the WHERE condition of the index I have one of the function arguments.
A plain "CREATE INDEX ... WHERE ..." will lead to a runtime error like this:

tmp2=# SELECT * FROM f_maint1( '20080401'::timestamptz );
ERROR: there is no parameter $1
CONTEXT: SQL statement "CREATE INDEX i_special_part ON t_atable( col1,col2 )
WHERE col3 >= $1 "
PL/pgSQL function "f_maint1" line 28 at SQL statement

To work this issue around I switched to dynamic SQL with "EXECUTE 'CREATE
INDEX ...' "
This means to me that the CREATE INDEX within a PL/pgSQL body cannot use
function arguments in the WHERE condition.

Is this the expected behavior?
If so, why?

--
Fahrbahn ist ein graues Band
weisse Streifen, grüner Rand

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Davis 2009-01-19 19:22:37 Re: array_agg for 8.3
Previous Message Faheem Mitha 2009-01-19 19:09:06 Re: array_agg for 8.3