On Monday 19 January 2009 23:28:08 Reg Me Please wrote: > On Monday 19 January 2009 22:49:17 Gerhard Heift wrote: > > 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 > > Exactly what I did: > > > To work this issue around I switched to dynamic SQL with "EXECUTE > > > 'CREATE INDEX ...' " > > The question remains, though. Is that a bug or is the documentation needing improvements? -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
