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