Re: How can I create null value from function call with no results?

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(at)gmail(dot)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
>
>