Discussion:
How can I create null value from function call with no results?
Seref Arikan
2014-07-30 18:13:00 UTC
Permalink
Greetings,
I want to call a function using a column of a table as the parameter and
return the parameter and function results together.
The problem is, when the function returns an empty row my select statement
that uses the function returns an empty row as well.

The following simplified snippet demonstrates the behaviour I'm trying to
change:


create or replace function test_empty_row(p_instance_id integer)
RETURNS TABLE (instance_id_int INTEGER, valstring TEXT)
AS
$$
BEGIN
return query SELECT 0, 'nothing'::text where 1 = 2;
END;
$$ LANGUAGE plpgsql;

select 1,test_empty_row(1); (this is actually "SELECT A.somefield,
myfunc(A.somefield) from my_table A" in my code)

The query above returns 0 rows. Instead of that I'd like to get back
1, null,null
when the query in the function returns zero results


I've been trying to do this in a number of ways for some time now, but I
guess I've run out of brain cells for today.

Regards
Seref
Pavel Stehule
2014-07-30 18:18:07 UTC
Permalink
Hello

you can try

world=# CREATE OR REPLACE FUNCTION xx(int)
world-# RETURNS TABLE(a int, b int) AS
world-# $$ SELECT $1, $1 WHERE $1 = 1 UNION ALL SELECT NULL, NULL LIMIT 1;
$$
world-# LANGUAGE sql;
CREATE FUNCTION
Time: 74.320 ms
world=# SELECT * FROM xx(1);
a | b
---+---
1 | 1
(1 row)

Time: 1.698 ms
world=# SELECT * FROM xx(2);
a | b
---+---
|
(1 row)

Regards

Pavel Stehule
Post by Seref Arikan
Greetings,
I want to call a function using a column of a table as the parameter and
return the parameter and function results together.
The problem is, when the function returns an empty row my select statement
that uses the function returns an empty row as well.
The following simplified snippet demonstrates the behaviour I'm trying to
create or replace function test_empty_row(p_instance_id integer)
RETURNS TABLE (instance_id_int INTEGER, valstring TEXT)
AS
$$
BEGIN
return query SELECT 0, 'nothing'::text where 1 = 2;
END;
$$ LANGUAGE plpgsql;
select 1,test_empty_row(1); (this is actually "SELECT A.somefield,
myfunc(A.somefield) from my_table A" in my code)
The query above returns 0 rows. Instead of that I'd like to get back
1, null,null
when the query in the function returns zero results
I've been trying to do this in a number of ways for some time now, but I
guess I've run out of brain cells for today.
Regards
Seref
Seref Arikan
2014-07-30 19:01:02 UTC
Permalink
Pavel, thank you so much. This did the trick!
Post by Pavel Stehule
Hello
you can try
world=# CREATE OR REPLACE FUNCTION xx(int)
world-# RETURNS TABLE(a int, b int) AS
world-# $$ SELECT $1, $1 WHERE $1 = 1 UNION ALL SELECT NULL, NULL LIMIT 1;
$$
world-# LANGUAGE sql;
CREATE FUNCTION
Time: 74.320 ms
world=# SELECT * FROM xx(1);
a | b
---+---
1 | 1
(1 row)
Time: 1.698 ms
world=# SELECT * FROM xx(2);
a | b
---+---
|
(1 row)
Regards
Pavel Stehule
Greetings,
Post by Seref Arikan
I want to call a function using a column of a table as the parameter and
return the parameter and function results together.
The problem is, when the function returns an empty row my select
statement that uses the function returns an empty row as well.
The following simplified snippet demonstrates the behaviour I'm trying to
create or replace function test_empty_row(p_instance_id integer)
RETURNS TABLE (instance_id_int INTEGER, valstring TEXT)
AS
$$
BEGIN
return query SELECT 0, 'nothing'::text where 1 = 2;
END;
$$ LANGUAGE plpgsql;
select 1,test_empty_row(1); (this is actually "SELECT A.somefield,
myfunc(A.somefield) from my_table A" in my code)
The query above returns 0 rows. Instead of that I'd like to get back
1, null,null
when the query in the function returns zero results
I've been trying to do this in a number of ways for some time now, but I
guess I've run out of brain cells for today.
Regards
Seref
David G Johnston
2014-07-30 18:26:29 UTC
Permalink
Post by Seref Arikan
select 1,test_empty_row(1);
SELECT 1, (SELECT test_empty_row(1)) AS func_result

You can also adjust the function to either return the result of the query OR
"RETURN NULL" if no results were found. i.e., do not use "RETURN QUERY"

David J.





--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-can-I-create-null-value-from-function-call-with-no-results-tp5813311p5813313.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Seref Arikan
2014-07-30 19:08:08 UTC
Permalink
Hi David,
Thanks for the feedback. I've actually tried to do what you've suggested,
but I always found myself unable to do the check for empty result for query
in an elegant way.
That is, I end up thinking about creating a temp table to put the query
results in (which can be > 1), then check if the temp table is empty and
return Null if that is the case.

Your response reads as if there is a more elegant way of doing this, could
you write down a dummy version of the alternative to return query you're
suggesting?

Kind regards
Seref

Ps: sorry for the double post David


On Wed, Jul 30, 2014 at 7:26 PM, David G Johnston <
Post by David G Johnston
Post by Seref Arikan
select 1,test_empty_row(1);
SELECT 1, (SELECT test_empty_row(1)) AS func_result
You can also adjust the function to either return the result of the query OR
"RETURN NULL" if no results were found. i.e., do not use "RETURN QUERY"
David J.
--
http://postgresql.1045698.n5.nabble.com/How-can-I-create-null-value-from-function-call-with-no-results-tp5813311p5813313.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
http://www.postgresql.org/mailpref/pgsql-general
Tom Lane
2014-07-30 18:54:11 UTC
Permalink
Post by Seref Arikan
I want to call a function using a column of a table as the parameter and
return the parameter and function results together.
The problem is, when the function returns an empty row my select statement
that uses the function returns an empty row as well.
This function isn't actually returning an empty row; it's returning no
rows, which is possible because RETURNS TABLE is really RETURNS SETOF
some-record-type. It's not entirely clear what you're trying to
accomplish, so the first thing is to get clear on that. Perhaps you
want it to always return one row? If so, don't use the TABLE notation
(just list some OUT parameters instead). If you actually do want it
to return zero rows, then the problem is not with the function but with
the query you're using it in. Set-returning functions in a SELECT's
targetlist are often a bad idea.

regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Seref Arikan
2014-07-30 19:20:39 UTC
Permalink
Thanks Tom,
The function can return multiple rows. It is a very simplified version of a
function that is used in the context of an EAV design.
It should return 0 or more rows that match the criteria that is calculated
in the function.

Originally I had a left outer join from the table that I'm using in the
SELECT here to a subquery. The problem is, postgres 9.3 chooses an
inefficient query plan when I do that (and this is all in a quite large
query).
If I replace the LEFT OUTER JOIN + subquery with the function call
approach, the overall query runs a lot faster. So it is a workaround for
performance reasons, though it leaves a bad taste in my mouth as well :(

Best regards
Seref
Post by Seref Arikan
Post by Seref Arikan
I want to call a function using a column of a table as the parameter and
return the parameter and function results together.
The problem is, when the function returns an empty row my select
statement
Post by Seref Arikan
that uses the function returns an empty row as well.
This function isn't actually returning an empty row; it's returning no
rows, which is possible because RETURNS TABLE is really RETURNS SETOF
some-record-type. It's not entirely clear what you're trying to
accomplish, so the first thing is to get clear on that. Perhaps you
want it to always return one row? If so, don't use the TABLE notation
(just list some OUT parameters instead). If you actually do want it
to return zero rows, then the problem is not with the function but with
the query you're using it in. Set-returning functions in a SELECT's
targetlist are often a bad idea.
regards, tom lane
Marc Mamin
2014-07-31 18:34:29 UTC
Permalink
Post by Seref Arikan
I want to call a function using a column of a table as the parameter and
return the parameter and function results together.
The problem is, when the function returns an empty row my select statement
that uses the function returns an empty row as well.
Hello,
not sure if it makes sense in your context, but something like this could do the job:

WITH SEL AS ( Your Query)
SELECT * FROM SEL
UNION ALL
SELECT 'nothing found' WHERE NOT EXISTS ( select * from sel);

regards,

Marc Mamin

Loading...