[SQL] parse error in create index

[Hubert Palme]

Hi,

could someone, please, explain me the following parse error?

adressen=> \d geburtstage
Table= geburtstage
+--+--+---+
|  Field   |  Type|
Length|
+--+--+---+
| lfd_nr   | int4
| 4 |
| geburtstag   | date
| 4 |
+--+--+---+
adressen=> create index Monat_Tag on geburtstage (date_part('month',
Geburtstag));
ERROR:  parser: parse error at or near "'"
adressen=>

Thanks in advance,

-- 
Hubert Palme
[EMAIL PROTECTED]

Re: [SQL] parse error in create index

[Hubert Palme]

Stephan Szabo wrote:
> 
> Functional indexes cannot currently take constant values to the function,
> so it's complaining about the constant 'month'.  The current workaround is
> probably to create a function that does the date_part('month', ) for
> you and then use that function in the index creation.

Hmm... Perhaps, it's better I post to the novice group, because I'm new
to SQL. 

Anyway -- That's my trial:

adressen=> CREATE FUNCTION geb_monat (date) RETURNS integer AS
adressen-> 'SELECT date_part('month', $1)::integer;'
adressen-> LANGUAGE 'sql';
ERROR:  parser: parse error at or near "month"

The point are the nested strings, I guess. How can I render a "'" in an
SQL string?

Thanks for your help!

-- 
Hubert Palme
[EMAIL PROTECTED]

Re: [SQL] parse error in create index

[Hubert Palme]

Stephan Szabo wrote:
> 
> Functional indexes cannot currently take constant values to the function,
> so it's complaining about the constant 'month'.  The current workaround is
> probably to create a function that does the date_part('month', ) for
> you and then use that function in the index creation.

OK, I got it now -- good old pascal/FORTRAN fashion. But now I get

adressen=> CREATE INDEX xxx ON geburtstage (geb_monat(geburtstag));
ERROR:  DefineIndex: (null) class not found
adressen=>

What is a class in this sense, and where can I read about it in the
documentation?
(geburtstag is a row of type DATE in the table geburtstage)

-- 
Hubert Palme
[EMAIL PROTECTED]

Re: [SQL] parse error in create index

[Hubert Palme]

Stephan Szabo wrote:
> 
> Functional indexes cannot currently take constant values to the function,
> so it's complaining about the constant 'month'.  The current workaround is
> probably to create a function that does the date_part('month', ) for
> you and then use that function in the index creation.

Hmm... Perhaps, it's better I post to the novice group, because I'm new
to SQL. 

Anyway -- That's my trial:

adressen=> CREATE FUNCTION geb_monat (date) RETURNS integer AS
adressen-> 'SELECT date_part('month', $1)::integer;'
adressen-> LANGUAGE 'sql';
ERROR:  parser: parse error at or near "month"

The point are the nested strings, I guess. How can I render a "'" in an
SQL string?

Thanks for your help!

-- 
Hubert Palme
[EMAIL PROTECTED]