Find sum of max values in a single SELECT query

Question

I have prepared an SQL Fiddle for my question.

In a word game I run a custom function to find all words played by a user in her last turn:

Invalid words have a score of NULL (could be changed to -1 if needed).

Valid words have positive score and if there are several matching words, then I only need the highest score (and discard the other score).

For example, if a player plays horizontal word "ab" with score 8 and vertical word "ab" with score 2, then she only gets 8 points for that turn.

Here is my test table:

CREATE TABLE words(word varchar, score integer);

And here I fill it with test data:

INSERT INTO words (word, score) VALUES
('ab', 8),  -- word with higher score should be taken
('ab', 2),  -- same word with lower score should be discarded
('xy', 2),
('zz', NULL); -- invalid word marked by NULL (or -1)

I can see if an invalid word has been played by

IF EXISTS (SELECT 1 FROM words WHERE score IS NULL) THEN
    RAISE EXCEPTION 'Invalid word played';
END IF;

And I can remove duplicate words with GROUP BY:

SELECT word, max(score) as score FROM words GROUP BY word;

My question is however:

How to combine both statements above to a single SELECT statement, so that I:

1. Know if an invalid word has been played
2. The sum of played scores (so that I can update the player's score)

I am looking for a single statement, so that the custom function is not run several times and preferably without a temporary table.

The result should look like (I am going to call it from another custom PL/pgSQL function):

DECLARE
    total_user_score    integer;
    invalid_words_found boolean;

SELECT
    .....,              -- how to calculate this value please?
    .....               -- how to calculate this value please?
INTO STRICT
    total_user_score,
    invalid_words_found
FROM words_check_words(....);  -- avoid calling this function twice

IF invalid_words_found THEN
    RAISE EXCEPTION "Invalid words found";
ELSE
    UPDATE games SET user_score = user_score + total_user_score;
END IF;

Answer 1

(edited to return a boolean for invalid_words_found)
(edited to use bool_or aggregate function)

If I understood correctly:

with cte as (
    select max(score) as score,
           bool_or(score is null) as has_invalid
      from words_check_words(....)
     group by word
)
select coalesce(sum(score), 0) as total_user_score,
       bool_or(has_invalid) as invalid_words_found
  from cte

Your custom function would only get called once.

EDIT: Integrating into your procedure, it would look something like this:

DECLARE
    total_user_score    integer;
    invalid_words_found boolean;

with cte as (
    select max(score) as score,
           bool_or(score is null) as has_invalid
      from words_check_words(....)
     group by word
)
select coalesce(sum(score), 0),
       bool_or(has_invalid)
INTO STRICT
    total_user_score,
    invalid_words_found
FROM cte;

IF invalid_words_found THEN
    RAISE EXCEPTION "Invalid words found";
ELSE
    UPDATE games SET user_score = user_score + total_user_score;
END IF;