Обсуждение: My second PL/pgSQL function - minor problem
Hey all,
http://pastebin.com/f1015226d
The above function appears to be doing exactly what I want: Fetch the users data and update the visits column, if the supplied credentials match a user.
My problem is what happens when the credentials don't match a user. In those cases I would like to have a boolean false returned, but instead I get an empty array - which of course is entirely according to the functions "RETURN SETOF users" statement.
Is this little problem solvable, or will I have to learn to live with the empty array?
Regards,
Thomas
http://pastebin.com/f1015226d
The above function appears to be doing exactly what I want: Fetch the users data and update the visits column, if the supplied credentials match a user.
My problem is what happens when the credentials don't match a user. In those cases I would like to have a boolean false returned, but instead I get an empty array - which of course is entirely according to the functions "RETURN SETOF users" statement.
Is this little problem solvable, or will I have to learn to live with the empty array?
Regards,
Thomas
In response to Thomas Løcke : > Hey all, > > http://pastebin.com/f1015226d > > The above function appears to be doing exactly what I want: Fetch the users > data and update the visits column, if the supplied credentials match a user. > > My problem is what happens when the credentials don't match a user. In those > cases I would like to have a boolean false returned, but instead I get an empty > array - which of course is entirely according to the functions "RETURN SETOF > users" statement. > > Is this little problem solvable, or will I have to learn to live with the empty > array? You can't return different return-types. Either return forever a record or an bool. But you can use select exists( select * from log_in( ...) ) to get true/false it there a user or not. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
On 2009-11-18, Thomas Løcke <thomas.granvej6@gmail.com> wrote: > --001517402ba882fac30478a73b83 > Content-Type: text/plain; charset=ISO-8859-1 > > Hey all, > > http://pastebin.com/f1015226d > > The above function appears to be doing exactly what I want: Fetch the users > data and update the visits column, if the supplied credentials match a user. > > My problem is what happens when the credentials don't match a user. In those > cases I would like to have a boolean false returned, but instead I get an > empty array - which of course is entirely according to the functions "RETURN > SETOF users" statement. > > Is this little problem solvable, or will I have to learn to live with the > empty array? array? how are you calling it? your function seems basically equivalent to UPDATE users SET lastvisit = now(), visits = visits + 1 WHERE username = uname AND password = passwd RETURNING id; anyway you'll have to look at the number of results returned, or re-write it to only return a single result (I'm assumuing you don't allow multiple accounts with the same username and password) then you can return a special value for not found (perhaps 0) personally I'd go with the sql given above and use apropriate means to ask how many results were returned. pg_num_rows(), FOUND, etc... OTOH you say array, perhaps your chosen platform already treats an array with zero elemants like false?
On 2009-11-20, Jasen Betts <jasen@xnet.co.nz> wrote: > On 2009-11-18, Thomas Løcke <thomas.granvej6@gmail.com> wrote: >> --001517402ba882fac30478a73b83 >> Content-Type: text/plain; charset=ISO-8859-1 >> >> Hey all, >> >> http://pastebin.com/f1015226d > your function seems basically equivalent to > > UPDATE users SET lastvisit = now(), visits = visits + 1 > WHERE username = uname AND password = passwd > RETURNING id; -------------- should be: RETURNING *; oops.