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 <serefari...@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 > >
