how to implement contains operator in Postgres

Question

How to implement contains operator for strings which returns true if left string contains in right string.

Operator name can be any. I tried @@ and code below but

select 'A' @@ 'SAS'

returns false.

How to fix ?

CREATE OR REPLACE FUNCTION public.contains(searchFor text, searchIn text)
RETURNS bool

AS $BODY$ BEGIN

RETURN position( searchFor in searchIn)<>0;

END; $BODY$ language plpgsql immutable  RETURNS NULL ON NULL INPUT;

CREATE OPERATOR public.@@ (
    leftarg = text,
    rightarg = text,
    procedure = public.contains
);

Using Postgres 9.1 and above in windows and linux.

select contains('A' , 'SAS' )

returns true as expected.

Update

I tried in 9.1 code from answer:

CREATE OR REPLACE FUNCTION public.contains(searchFor text, searchIn text)
RETURNS bool
LANGUAGE sql IMMUTABLE
AS $BODY$
SELECT position( searchFor in searchIn )<>0;
$BODY$;


CREATE OPERATOR public.<@ (
    leftarg = text,
    rightarg = text,
    procedure = public.contains
);

but got error

ERROR:  column "searchin" does not exist
LINE 5: SELECT position( searchFor in searchIn )<>0;

How to make it work in 9.1 ? In 9.3 it works.

Using

"PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit"

Craig Ringer · Accepted Answer · 2014-07-21 01:51:00Z

2

PostgreSQL already defines an @@ operator on (text,text) in the pg_catalog schema, which is defined as:

regress=> \do @@
                                  List of operators
   Schema   | Name | Left arg type | Right arg type | Result type |    Description    
------------+------+---------------+----------------+-------------+-------------------
 pg_catalog | @@   | text          | text           | boolean     | text search match

That is taking precedence over the @@ you defined in the public schema.

I suggest using the operator <@ for contains, because that's consistent with the array contains and contained-by operators. There's no usage of it in pg_catalog for text ... but there's no guarantee one won't be added in a future version or by an extension.

If you want to guarantee that your operators take precedence over those in pg_catalog, you need to put them in a different schema and put it first on the search path, explicitly before pg_catalog. So something like:

CREATE SCHEMA my_operators;

CREATE OR REPLACE FUNCTION my_operators.contains(searchFor text, searchIn text)
RETURNS bool
LANGUAGE sql IMMUTABLE
AS $BODY$
SELECT position( searchFor in searchIn)<>0;
$BODY$;

CREATE OPERATOR my_operators.<@ (
    leftarg = text,
    rightarg = text,
    procedure = public.contains
);

then

SET search_path = 'my_operators, pg_catalog, public';

which you can do with ALTER USER and/or ALTER DATABASE to make it a default.

edited Jul 21, 2014 at 1:51

answered Jul 21, 2014 at 1:45

Craig Ringer

329k84 gold badges743 silver badges821 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Andrus Over a year ago

Answer does not contain RETURNS NULL ON NULL INPUT . Should it added to help planner? Is it reasonable and possible to add COMMUTATOR, RESTRICT, JOIN, HASHES, MERGES clauses ? Is it better to use language sql instead of pgsql ?

Craig Ringer Over a year ago

@Andrus Actually RETURNS NULL ON NULL INPUT and STRICT can inhibit inlining, and are better omitted if possible. The documentation needs to be clearer about that. Yes, it's quite reasonable to add a commutator @> operator. I don't see how you'd implement hashing.

Andrus Over a year ago

How to create operator only if it does not exist ? I tried CREATE OPERATOR IF NOT EXISTS public.<@ but got error

Craig Ringer Over a year ago

@Andrus Use a DO block that looks it up in pg_operator, I guess. Not every statement type has IF NOT EXISTS. Personally I strongly suggest packaging it in an extension instead. The documentation covers the creation of extensions. You don't need any C code for an extension, it can be just an SQL file, Makefile and extension control file.

Craig Ringer Over a year ago

@Andrus replace searchfor with $1 and searcin with $2

|

Collectives™ on Stack Overflow

how to implement contains operator in Postgres

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related