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