[SQL] Immutable function in index

[SZŰCS Gábor]

Dear Gurus,

Version: PostgreSQL 7.3.3
Log:
%--- cut here ---%
tir=# CREATE INDEX ajanlat_cikk ON ajanlat (antinuller(cikk),
antinuller(minoseg), tol);
ERROR:  parser: parse error at or near "," at character 55
tir=# CREATE INDEX ajanlat_szolg ON ajanlat (antinuller(szolgaltatas), tol);
ERROR:  parser: parse error at or near "," at character 64
%--- cut here ---%

I'm not sure what happened, I'm quite sure it worked before, even with
7.3.3. The index was there and probably worked, but accidentally dropped it
and can't recreate.
May it be something in postgresql.conf (fumbled with it recently)?
May it be a bug in 7.3.3 (despite my memories)?

Relevant definitions:

%--- cut here ---%
CREATE FUNCTION antinuller (int4) RETURNS int4 AS '
SELECT CASE WHEN $1 ISNULL THEN -1 ELSE $1 END;'
LANGUAGE 'SQL' immutable;

CREATE TABLE ajanlat (
az serial NOT NULL PRIMARY KEY,
cikk integer REFERENCES cikk(az) ON UPDATE CASCADE,
minoseg integer REFERENCES minoseg(az) ON UPDATE CASCADE,
-- a minőség default 1 triggerből, mert csak ha nem szolgáltatás!
szolgaltatas integer REFERENCES szolgaltatas(az) ON UPDATE CASCADE,
tol date DEFAULT now() NOT NULL,
ig date DEFAULT '3000-01-01' NOT NULL,
CONSTRAINT kitoltes CHECK
((cikk ISNULL = minoseg ISNULL) AND (cikk ISNULL = szolgaltatas
NOTNULL))
);

CREATE INDEX ajanlat_cikk ON ajanlat (antinuller(cikk), antinuller(minoseg),
tol);
CREATE INDEX ajanlat_szolg ON ajanlat (antinuller(szolgaltatas), tol);
%--- cut here ---%

TIA,
G.
%--- cut here ---%
\end


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

   http://archives.postgresql.org

Re: [SQL] Immutable function in index

[Tom Lane]

"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <[EMAIL PROTECTED]> writes:
> tir=# CREATE INDEX ajanlat_cikk ON ajanlat (antinuller(cikk),
> antinuller(minoseg), tol);
> ERROR:  parser: parse error at or near "," at character 55

> I'm not sure what happened,

You're trying to use a 7.4 feature in 7.3.  Multiple functional columns
in one index is new in 7.4.

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] Why no exprs in format string to RAISE

[Bruce Momjian]

Richard Huxton wrote:
> Markus Bertheau wrote:
> > Hi,
> > 
> > is there a non-implementation reason as to why there are no expressions
> > allowed in the arguments to the format string to RAISE, or is that just
> > not implemented yet?
> 
> Just not done yet AFAIK. It would require some changes to the parser for 
>   plpgsql, but nothing too extreme. Might make a good project for 
> someone learning more about PG's code.

TODO has:

o Allow PL/PgSQL's RAISE function to take expressions

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html