I am using Postgres 9.4.6.
I have created a Function for finding email address that match an email variable that is passed. However, when I search for 'email@example.com' I get a full table of email addresses that do not match 'firstname.lastname@example.org'. My function is as follows:-
CREATE OR REPLACE FUNCTION spgetmatchingusers(IN email text) RETURNS TABLE(email character, password character, userid smallint, teamname character, reset_password character, reset_password_expiry timestamp with time zone) AS $BODY$ select "email", "password", "userid", "teamname", "reset_password", "reset_password_expiry" from "RegisteredUsers" where LOWER("RegisteredUsers"."email") = LOWER(email) $BODY$ LANGUAGE sql VOLATILE COST 100 ROWS 1000; ALTER FUNCTION spgetmatchingusers(text) OWNER TO bjlizgykrnyffr;
I call the function with:
select * from spgetmatchingusers('email@example.com')
If I type:
select * from spgetmatchingusers('randomtext')
I still get a full table of users returned.
For reference, my RegisteredUsers table is as follows...
CREATE TABLE "RegisteredUsers" ( userid smallint NOT NULL DEFAULT nextval('user_id_seq'::regclass), teamname character(50) NOT NULL, first_name character(50), surname character(50), email character(50), company_name character(50), password character(50), reset_password character(50), reset_password_expiry timestamp with time zone, CONSTRAINT "RegisteredUsers_pkey" PRIMARY KEY (userid) ) WITH ( OIDS=FALSE ); ALTER TABLE "RegisteredUsers" OWNER TO bjlizgykrnyffr;
Apologies if I have missed something obvious but after searching around the internet I can't seem to work out where I have gone wrong!