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');
Written by Anders Brownworth
Related protips
Have a fresh tip? Share with Coderwall community!
Post
Post a tip
Best
#Sql
Authors
Sponsored by #native_company# — Learn More
#native_title#
#native_desc#