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

Pavel, thank you so much. This did the trick!

On Wed, Jul 30, 2014 at 7:18 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

> 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
>>
>>
>