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 'user@email.com' I get a full table of email addresses that do not match 'user@email.com'. 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
select "email", "password", "userid", "teamname", "reset_password", "reset_password_expiry"
from "RegisteredUsers"
where LOWER("RegisteredUsers"."email") = LOWER(email)
COST 100
ROWS 1000;
ALTER FUNCTION spgetmatchingusers(text)
OWNER TO bjlizgykrnyffr;

I call the function with:

select * from spgetmatchingusers('user@email.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)
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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.