Postgres Pro
Downloads
Home   >   mailing lists

Thread: Functional index adding one

Functional index adding one

From
lbarcala@freeresearch.org
Date:
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?


CREATE TABLE doc(
  id INT,
  editorial VARCHAR,
  CONSTRAINT doc_pk PRIMARY KEY (id)
);

CREATE TABLE token (
  id INT,
  id_doc INT,
  token VARCHAR,
  position INT,
  CONSTRAINT foreign_doc FOREIGN KEY (id_do)
    REFERENCES doc (identificador)
);

Regards,

  Mario Barcala

Re: Functional index adding one

From
"A. Kretschmer"
Date:
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

Re: Functional index adding one

From
Sam Mason
Date:
On Thu, Jul 03, 2008 at 11:50:39AM +0200, lbarcala@freeresearch.org wrote:
> 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?

You just want an extra set of brackets; i.e.:

  CREATE INDEX token_position_func ON token ((position+1));

Should do the trick.  Not entirely sure why, but it'll probably have
something to do with avoiding ambiguity in the grammar.


  Sam

Re: Functional index adding one

From
Gregory Stark
Date:
<lbarcala@freeresearch.org> writes:

> 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 think you just need another set of parentheses:

CREATE INDEX token_position_func on (token(position+1))

Unless you're on a very old version of Postgres, I think 7.3 which had
functional indexes but not expression indexes.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning

Re: Functional index adding one

From
Gregory Stark
Date:
"Gregory Stark" <stark@enterprisedb.com> writes:

> CREATE INDEX token_position_func on (token(position+1))

Ooops, I misread that as if "token" were a function and not the table. Sam
Mason had the right syntax. Sorry.


--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!

Re: Functional index adding one

From
Tom Lane
Date:
Sam Mason <sam@samason.me.uk> writes:
> You just want an extra set of brackets; i.e.:

>   CREATE INDEX token_position_func ON token ((position+1));

> Should do the trick.  Not entirely sure why, but it'll probably have
> something to do with avoiding ambiguity in the grammar.

Right.  The problem is the Berkeley-era decision to put index opclasses
into the syntax without any keyword or punctuation, viz

    create index ... on table (column_name [ opclass_name ]);

So something like

    CREATE INDEX token_position_func ON token (a + b);

is ambiguous: is the + an infix operator, or is it a postfix operator
and the "b" is to be taken as an opclass name?

We hacked around that by requiring parens around expressions.  For
backwards compatibility with other Berkeley-era syntax, there's
a special exception that you can omit the parens when the expression is
just a function call.

            regards, tom lane

Re: Functional index adding one

From
Sam Mason
Date:
On Thu, Jul 03, 2008 at 11:11:26AM -0400, Tom Lane wrote:
> Sam Mason <sam@samason.me.uk> writes:
> > Not entirely sure why, but it'll probably have
> > something to do with avoiding ambiguity in the grammar.
>
> Right.  The problem is the Berkeley-era decision to put index opclasses
> into the syntax without any keyword or punctuation, viz
>
>     create index ... on table (column_name [ opclass_name ]);
>
> So something like
>
>     CREATE INDEX token_position_func ON token (a + b);
>
> is ambiguous: is the + an infix operator, or is it a postfix operator
> and the "b" is to be taken as an opclass name?

In this case it seems unambiguous, but in general I can see why.
Hysterical raisins cause all sorts of fun!

> We hacked around that by requiring parens around expressions.  For
> backwards compatibility with other Berkeley-era syntax, there's
> a special exception that you can omit the parens when the expression is
> just a function call.

I'm tempted to say "how about putting another example in the page" but
it seems to document the issue quite well already.

I've never liked to putting comments into the interactive version of the
manual, mainly because they disappear with each major version, but how
about having the manual link to the wiki?  At the moment, the wiki seems
somewhat isolated and I never seem to find myself using it.  If there
were some nice trails in, maybe it would be used more; somewhere like:

  http://wiki.postgresql.org/wiki/Manual/sql-createindex

I wouldn't worry about versioning, just let the authors of the pages
deal with versioning issues in ways appropriate to the subject.  Or has
this idea been dismissed before?


  Sam