Re: How to force select to return exactly one row

----- Original Message -----

From: Martin

To: Andrus

Cc: pgsql-general@postgresql.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@hot.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general