On Mon, Jan 19, 2009 at 08:19:06PM +0100, Reg Me Please wrote: > 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 ...' " What about EXECUTE 'CREATE INDEX bla ON t_table WHERE (col >= ' || pg_catalog.quote_literal($1) || ')'; Regards, Gerhard
Attachment:
signature.asc
Description: Digital signature
