Re: Functional index adding one

am  Thu, dem 03.07.2008, um 11:50:39 +0200 mailte lbarcala@freeresearch.org folgendes:
> Hi all:
>
> I'm trying to create a functional index into column position of token
> table (see below). I want to make something like:
>
> CREATE INDEX token_position_func
> ON token (position+1);
>
> but I get:
>
> test=# CREATE INDEX token_position_func
> test-# ON token (position+1);
> ERROR:  syntax error at or near "+"
> LINE 2: ON token (position+1);
>
> I read that I can do "ON function(column)" but, is there a built-in
> function in PostgreSQL to do what I want (add one to the value) or have i
> to build one to make this simple calculation?

Right, write your own function for that, for example:

test=# CREATE TABLE token (id int, id_doc int, token text, position int);
CREATE TABLE
test=*# create function get_pos(int) returns int as $$
  declare ret int;
  begin
    select into ret position+1 from token where id=$1;
    return ret;
  end;
$$language plpgsql immutable;
CREATE FUNCTION
test=*# CREATE INDEX token_position_func ON token (get_pos(position));
CREATE INDEX


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net