Re: How to force select to return exactly one row - Mailing list pgsql-general
| From | Martin |
|---|---|
| Subject | Re: How to force select to return exactly one row |
| Date | |
| Msg-id | AANLkTilArZpLvg55YmNEpANS3fhs6LTDPC36IFdt8d5O@mail.gmail.com Whole thread Raw |
| In response to | Re: How to force select to return exactly one row ("Andrus" <kobruleht2@hot.ee>) |
| Responses |
Re: How to force select to return exactly one row
|
| List | pgsql-general |
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@hot.ee> wrote:
Martin,Thank you. SELECT statement returns lot of columns.I triedselect coalesce( (select 1,2 ), null);but gotERROR: subquery must return only one columnHow to use your suggestion if select returns lot of columns ?Andrus.----- Original Message -----From: MartinTo: AndrusSent: Monday, June 21, 2010 10:14 PMSubject: Re: [GENERAL] How to force select to return exactly one rowTry wrapping the entire statement in a COALESCE((statement), <DEFAULT_VALUE>);-m2010/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
pgsql-general by date: