Re: How to force select to return exactly one row

Ah yes sorry I missed the multi-columns. "My way" doesn't work for that.
If Tim's suggestion doesn't work for you, you could try a union...
it's fairly nasty and you will always have your "fake" row in the result.

Also I too am confused by "empty row". Are you trying to loop through the
results in code and it fails if there are no rows at all?
Or some other equally odd thing? =)

Anyway here is an example UNION that I think would work (but note, this row
will always be included even when your statement returns something, so it
might not work for you).

(YOUR SELECT HERE)
UNION
(SELECT '','',1,1,perfectly_matched_datatype_cols_here); --those first
couple are just examples

Mind you, I think this is nasty and would highly suggest taking another look
at the code that is using this statement to see if you can deal more
gracefully with an empty resultset.

hope this helps,
-m

On Mon, Jun 21, 2010 at 12:32 PM, Andrus <kobruleht2(at)hot(dot)ee> wrote:

> Martin,
>
> Thank you. SELECT statement returns lot of columns.
>
> I tried
>
> select coalesce( (select 1,2 ), null);
>
> but got
>
> ERROR: subquery must return only one column
>
> How to use your suggestion if select returns lot of columns ?
>
> Andrus.
>
>
>
>
>
> ----- Original Message -----
> *From:* Martin <mgonzo(at)gmail(dot)com>
> *To:* Andrus <kobruleht2(at)hot(dot)ee>
> *Cc:* pgsql-general(at)postgresql(dot)org
> *Sent:* Monday, June 21, 2010 10:14 PM
> *Subject:* Re: [GENERAL] How to force select to return exactly one row
>
> Try wrapping the entire statement in a COALESCE((statement),
> <DEFAULT_VALUE>);
>
> -m
>
> 2010/6/21 Andrus <kobruleht2(at)hot(dot)ee>
>
>>
>> Autogenerated select statement contains 0 .. n left joins:
>>
>> SELECT somecolumns
>> FROM ko
>> LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
>> ...
>> LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
>> WHERE ko.primarykey='someprimarykeyvalue';
>>
>> This select can return only 0 or 1 rows depending if ko row with primary
>> key
>> 'someprimarykeyvalue' exists or not.
>>
>> Problem:
>>
>> if there is no searched primary key row in ko database, select should also
>> return empty row.
>>
>> To get this result I added right join:
>>
>> SELECT somecolumns
>> FROM ko
>> RIGHT JOIN (SELECT 1) _forceonerow ON true
>> LEFT JOIN t1 ON t1.primarykey= ko.t1foreignkwey
>> ...
>> LEFT JOIN tn ON tn.primarykey= ko.tnforeignkwey
>> WHERE ko.primarykey is null or ko.primarykey='someprimarykeyvalue';
>>
>> but it still does not return row if primary key row 'someprimarykeyvalue'
>> does not exist.
>>
>> How to force this statement to return one row always ?
>>
>> Andrus.
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
> Andrus Moor
> OÜ Eetasoft
> Akadeemia 21-G302
> Tallinn 12618
> www.eetasoft.ee
> tel. 6654214, 6654215
>