Re: to pg

Albe Laurenz <laurenz.albe@xxxxxxxxxx> · Fri, 25 Sep 2015 13:56:56 +0000

Ramesh T wrote:
> CREATE UNIQUE INDEX idx_load_pick ON  pick (case picked when picked='y' then load_id else null end );
> 
> how can i convert case expressed to postgres..above it is oracle.

CREATE TABLE pick (picked char(1), load_id integer);

CREATE FUNCTION picked_loadid(character, integer) RETURNS integer
   IMMUTABLE STRICT LANGUAGE sql AS
   $$SELECT CASE WHEN $1 = 'y' THEN $2 ELSE NULL END$$;

CREATE INDEX idx_load_pick ON pick (picked_loadid(picked, load_id));

*but*

It will only work with queries like:

SELECT * FROM pick WHERE picked_loadid(picked, load_id) IS NOT NULL;

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general