Index on computed column

[Han Holl]

Hi,

Can anyone explain to me what I'm doing wrong:
(The first select is to show that the function rubriek exists, and does work).
I want to create an index on a computed column:

palga=> select rubriek(rapport, lseek, 'naamvrouw',0) from main
where rapport = 'T098-20900';
rubriek
-----------
Ramrattan
(1 row)
palga=> create index nm_idx on main (rubriek(rapport, lseek, 'naamvrouw',0));
ERROR: parser: parse error at or near "'naamvrouw'" at character 54
palga=>

This is postgresql-7.3.4-3.rhl9.

Thanks in advance,

Han Holl

[Alvaro Herrera]

On Tue, Apr 20, 2004 at 12:48:42PM -0700, Han Holl wrote:

Can anyone explain to me what I'm doing wrong:
(The first select is to show that the function rubriek exists, and does work).
I want to create an index on a computed column:

Functional indexes could not have constants in 7.3; you can build a
wrapper function with the constant embedded, and create an index using
the wrapper function.

Or you can just use 7.4.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La virtud es el justo medio entre dos defectos" (Aristóteles)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Alvaro Herrera]

On Tue, Apr 20, 2004 at 12:48:42PM -0700, Han Holl wrote:

Can anyone explain to me what I'm doing wrong:
(The first select is to show that the function rubriek exists, and does work).
I want to create an index on a computed column:

Functional indexes could not have constants in 7.3; you can build a
wrapper function with the constant embedded, and create an index using
the wrapper function.

Or you can just use 7.4.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La virtud es el justo medio entre dos defectos" (Aristóteles)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Kris Jurka]

On Tue, 20 Apr 2004, Han Holl wrote:

palga=> create index nm_idx on main (rubriek(rapport, lseek, 'naamvrouw',0));
ERROR: parser: parse error at or near "'naamvrouw'" at character 54
palga=>

This is postgresql-7.3.4-3.rhl9.

You cannot create functional indexes with a constant in the 7.3 series.
This capability was only added in the 7.4 series. You can work around
this problem by creating a wrapper function that calls the real function
with the constants and creating the index on the wrapper function.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Kris Jurka]

On Tue, 20 Apr 2004, Han Holl wrote:

palga=> create index nm_idx on main (rubriek(rapport, lseek, 'naamvrouw',0));
ERROR: parser: parse error at or near "'naamvrouw'" at character 54
palga=>

This is postgresql-7.3.4-3.rhl9.

You cannot create functional indexes with a constant in the 7.3 series.
This capability was only added in the 7.4 series. You can work around
this problem by creating a wrapper function that calls the real function
with the constants and creating the index on the wrapper function.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Stephan Szabo]

On Tue, 20 Apr 2004, Han Holl wrote:

Hi,

Can anyone explain to me what I'm doing wrong:
(The first select is to show that the function rubriek exists, and does work).
I want to create an index on a computed column:

palga=> select rubriek(rapport, lseek, 'naamvrouw',0) from main
where rapport = 'T098-20900';
rubriek
-----------
Ramrattan
(1 row)
palga=> create index nm_idx on main (rubriek(rapport, lseek, 'naamvrouw',0));
ERROR: parser: parse error at or near "'naamvrouw'" at character 54
palga=>

This is postgresql-7.3.4-3.rhl9.

In 7.3, functional indexes can only be across columns of the table, so
something like the above is illegal. You can get around this my making a
function that hardcodes the constants and then use that in the index and
queries.

In 7.4, you can index a more general set of expressions.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Stephan Szabo]

On Tue, 20 Apr 2004, Han Holl wrote:

Hi,

Can anyone explain to me what I'm doing wrong:
(The first select is to show that the function rubriek exists, and does work).
I want to create an index on a computed column:

palga=> select rubriek(rapport, lseek, 'naamvrouw',0) from main
where rapport = 'T098-20900';
rubriek
-----------
Ramrattan
(1 row)
palga=> create index nm_idx on main (rubriek(rapport, lseek, 'naamvrouw',0));
ERROR: parser: parse error at or near "'naamvrouw'" at character 54
palga=>

This is postgresql-7.3.4-3.rhl9.

In 7.3, functional indexes can only be across columns of the table, so
something like the above is illegal. You can get around this my making a
function that hardcodes the constants and then use that in the index and
queries.

In 7.4, you can index a more general set of expressions.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Han Holl]

Hello Alvaro, Kris and Stephan,

Thank you for your help. I apologize for reacting so late, but I've
been on vacation.
I must say I'm amazed that I post a question on April 20, and get
three almost identical, equally helpful, reactions after six days,
posted in the same hour!

I'm puzzled but grateful,

Han Holl