Re: dunction issue

On Thu, Mar 27, 2008 at 08:43:46PM +0100, Alain Roger wrote:
> i have a problem solving my function trouble.
> this function should return an email address stored in a table
> (tmp_newsletterreg) based on a number (sessions ID).
> if the session id is not find it should return a string corresponding to and
> error.
> if the email in found but already exists into another table (users), it
> should also return a string value relative the this error.

This is fun isn't it!

> here is my stored procedure.

And here it is in a single, unreadable, SQL statement:

SELECT CASE WHEN s.email = u.email THEN 'email already exists'
ELSE COALESCE(s.email, 'no such session') END AS msg
FROM (VALUES (1)) x(one)
LEFT JOIN (
SELECT email FROM tmp_newsletterreg
WHERE sessionid = $1) s ON TRUE
LEFT JOIN (SELECT email FROM users) u ON s.email = u.email;

Why not put a foreign key on the "email" column to the users table---one
less error to handle that way?

Sam