Re: CREATE parametric partial INDEX within a function body - Mailing list pgsql-general

From Gerhard Heift
Subject Re: CREATE parametric partial INDEX within a function body
Date
Msg-id 20090119214916.GA6578@gheift
Whole thread Raw
In response to CREATE parametric partial INDEX within a function body  (Reg Me Please <regmeplease@gmail.com>)
Responses Re: CREATE parametric partial INDEX within afunction body  (Reg Me Please <regmeplease@gmail.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Ivan Sergio Borgonovo
Date:
Subject: Re: left join with smaller table or index on (XXX is not null) to avoid upsert
Next
From: Dennis C
Date:
Subject: pg_restore: [archiver] entry ID -825110830 out of range -- perhaps a corrupt TOC