How to return custom value from function call?

Question

I am trying to call a function called account.user_available_sel in PostgreSQL 13 which returns a table of data. It is very simple like this:

SELECT * FROM account.user_available_sel(_email => 'email@xxx.xxx');

The SELECT * part returns back the entire table of data which includes the user's email, password, forename, surname. dob etc.

Because this is only supposed to check if an email has not already been registered, I only want to return a simple value like invalid if a record exists. In my mind something like this (obviously does not work):

SELECT 'invalid' FROM account.user_available_sel(_email => 'email@xxx.xxx');

How could I just return that string instead of the entire recordset?

score 1 · Accepted Answer · answered Jun 27 '21 at 15:07

You can use EXISTS to get a Boolean.

SELECT EXISTS (SELECT *
                      FROM account.user_available_sel(_email => 'email@xxx.xxx'));

And if that isn't satisfying, you can of course slap a CASE around it and get self defined strings.

SELECT CASE
         WHEN EXISTS (SELECT *
                             FROM account.user_available_sel(_email => 'email@xxx.xxx')) THEN
           'existent'
         ELSE
           'non-existent'
       END;

Similarly you can do that for numbers or whatever you like.

How to return custom value from function call?

1 Answers1