Thread: CREATE parametric partial INDEX within a function body

CREATE parametric partial INDEX within a function body

From

Reg Me Please

Date:

19 January 2009, 18:22:35

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

Re: CREATE parametric partial INDEX within a function body

From

Gerhard Heift

Date:

19 January 2009, 20:49:29

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

Re: CREATE parametric partial INDEX within afunction body

From

Reg Me Please

Date:

19 January 2009, 21:30:46

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.

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

Re: CREATE parametric partial INDEX within afunction body

From

Reg Me Please

Date:

20 January 2009, 12:17:16

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