Search Postgresql Archives

Re: How to force select to return exactly one row

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxx> 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
To: Andrus
Sent: Monday, June 21, 2010 10:14 PM
Subject: 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@xxxxxx>

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@xxxxxxxxxxxxxx)
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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux