Обсуждение: How can I create null value from function call with no results?

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

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

2014-07-30 20:13 GMT+02:00 Seref Arikan <serefarikan@gmail.com>:

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

Seref Arikan wrote
> 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-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Seref Arikan <serefarikan@gmail.com> writes:
> 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