Content-type: text/plain; charset=us-ascii
MIME-version: 1.0 (Mac OS X Mail 10.2 \(3259\))
Subject: Re: simple "select / if found" isn't
From: Jan Otto <asche@me.com>
In-reply-to: <201612161102.37559.gary.stainburn@ringways.co.uk>
Date: Fri, 16 Dec 2016 12:34:48 +0100
Cc: pgsql-sql@postgresql.org
Content-transfer-encoding: quoted-printable
Message-id: <C9BFAD75-EB0B-4107-A6D9-31ED34D7AF6D@me.com>
References: <201612161102.37559.gary.stainburn@ringways.co.uk>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

hi gary,

> On 16 Dec 2016, at 12:02, Gary Stainburn <gary.stainburn@ringways.co.uk> =
wrote:
>=20
> I'm creating a simple function that must have been done millions of times=
=20
> before, but I can't get it to work.  In this case, I'm checking a user ID=
 and=20
> password against previously used passwords:
>=20
> All I want to do is return 'found' based on the select but I can't get it=
 to=20
> work.=20=20
>=20
> If I run=20
>=20
> select 1 from user_previous_passwords=20
> 	where u_id=3D25 and=20
> 	crypt('MyPaSSword',u_previous_password) =3D u_previous_password;
>=20
> then it returns the matching row(s)
>=20
> If I run my function
>=20
> create or replace function check_previous_passwords (ID int4, PASS varcha=
r)=20
> returns boolean as $$
> DECLARE
>  UID int4;
> BEGIN
>  return exists(select 1 from user_previous_passwords=20
>    where u_id=3DID and crypt(PASS,u_previous_password) =3D PASS);

return exists(select 1 from user_previous_passwords=20
   where u_id=3DID and crypt(PASS,u_previous_password) =3D u_previous_passw=
ord);

> END;
> $$ LANGUAGE plpgsql;

regards, jan

--=20
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql