Last Updated: February 25, 2016
·
285
· andersbrownwort

Postgres SQL Function with a LIKE

SQL functions that SELECT records explicitly are fairly straightforward:

CREATE OR REPLACE FUNCTION name_for_email(query varchar, OUT email varchar)
    RETURNS SETOF record
   AS
$$
    SELECT name 
    FROM customers 
    WHERE email = $1;
$$
  LANGUAGE 'sql';

and then SELECT like this:

SELECT * from name_for_email('joe@test.com');

SELECTing records that match a pattern, however, can be done like this:

CREATE OR REPLACE FUNCTION name_for_email(query varchar, OUT email varchar)
    RETURNS SETOF record
   AS
$$
    SELECT name 
    FROM customers
    WHERE email LIKE $1;
$$
  LANGUAGE 'sql';

and then SELECT by passing the pattern:

SELECT * from name_for_email('%@test.com');