"Kynn Jones" <kynnjo@gmail.com> writes:
> CREATE OR REPLACE FUNCTION canonicalize( anyelement, anyelement )
> RETURNS anyarray AS
> $$
> BEGIN
> IF $1 < $2 THEN RETURN ARRAY[ $1, $2 ];
> ELSE RETURN ARRAY[ $2, $1 ];
> END IF;
> END;
> $$ LANGUAGE plpgsql;
You need to add IMMUTABLE as well.
> and this function works as expected, but when I try to use it in a
> constraint I get the error:
>
> -> ALTER TABLE foo ADD CONSTRAINT foo_uniq_x_y UNIQUE(canonicalize(x,y));
> ERROR: 42601: syntax error at or near "("
> LINE 1: ...E foo ADD CONSTRAINT foo_uniq_x_y UNIQUE(canonicalize(x,y));
What you need is:
CREATE UNIQUE INDEX foo_uniq_x_y on foo (canonicalize(x,y));
> LOCATION: base_yyerror, scan.l:795
>
> I found this puzzling; it's not clear to me why UNIQUE(UPPER(x)) is OK
> syntax but not UNIQUE(my_function(x)).
Really? It doesn't work for me in the ADD CONSTRAINT syntax. I don't think you
can use the ADD CONSTRAINT syntax, you have to use the CREATE UNIQUE INDEX
syntax. It's effectively the same in Postgres anyways.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
