From: | Reg Me Please <regmeplease(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: CREATE parametric partial INDEX within a function body |
Date: | 2009-01-20 13:16:39 |
Message-ID: | 200901201416.39128.regmeplease@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Csaba Együd | 2009-01-20 14:03:33 | Database schema & data synchronizer software for PostgreSQL? |
Previous Message | Albe Laurenz | 2009-01-20 13:16:20 | Re: Can I separate my Databases as in FireBird? Working with DataBases... |