0
I wonder if there is any way to return a value even if the result of the query is null, for example in the query below I try to return the id 0 case to table1 be it null. I tried to use CROSS JOIN but did not have good results.
SELECT COALESCE(tab.id, 0) id_tabela FROM table1 tab WHERE tab.id IS NULL
0
You can write a Function for this:
CREATE OR REPLACE FUNCTION esta_vazia() RETURNS text language plpgsql as $_$
DECLARE
registros record;
BEGIN
SELECT *
INTO registros
FROM table1;
IF (registros isnull) THEN
RETURN 'a tabela está vazia';
END IF;
RETURN 'Tabela ocupada';
END;
$_$;
To test if the result uses:
select esta_vazia();
Note the result
Browser other questions tagged postgresql query join
You are not signed in. Login or sign up in order to post.
I don’t see any scenario that returning a "fake record" would be a good thing. Why do you want this?
– Tiedt Tech
What do you mean? "Spontaneous generation"? Coming out of nowhere?
– anonimo
I am building a report, but if the line of my select returns a null value I need to create a fake line with zeroed values, I thought to do this in Java, but it would give me a lot of work, so I’m trying to solve via select. @Tiedttech
– Allan David
Maybe what you need is to use the function
COALESCE. But see that you are not creating something nonexistent is only replacing the display of NULL values. I don’t see where a CROSS JOIN would help your problem.– anonimo
So what I’m looking for is a way to create this data, and as I said,
COALESCE, not helping– Allan David
I made a simple example at http://sqlfiddle.com/#! 15/eb4fb/4. Look if this is what you want? The only problem with this SQL is that it will have to replicate the
where– Tiedt Tech
It’s okay to replicate
where, in my case, I think your solution solves my problem. Thank you so much for helping, and sorry for any wrong post formatting or lack of clarity. @Tiedttech– Allan David