Re: [GENERAL] How to force select to return exactly one row

[Andrus]

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 
  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 <kobrule...@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



Andrus Moor
OÜ Eetasoft
Akadeemia 21-G302
Tallinn 12618
www.eetasoft.ee
tel. 6654214,  6654215