1

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
$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('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)
)
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!

3

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.