Re: How to return a default value if no result

For a really clean solution you would want to create a composite type.

SELECT COALESCE ( (SELECT ROW(...,...,...)::type FROM ...),
ROW(val,val,val)::type )

You can make use of an anonymous/record type here but you will then be
unable to expand it back into individual columns.

The "ROW" is optional but makes it clear in examples like this what is
intended.

see the CREATE TYPE documentation for the syntax to create a custom
composite type.

David J.

On Fri, Aug 22, 2014 at 11:33 AM, Rob Northcott [via PostgreSQL] <
ml-node+s1045698n5815879h73(at)n5(dot)nabble(dot)com> wrote:

> Thanks David,
> That's actually what we did in the end, works fine in the case where there
> is only one field (sorry, column!) in the result.
> Just out of curiosity (luckily all the places I need to do this at the
> moment will work with coalesce), is there a way to return a default result
> with a multiple-column query, without resorting to unions or the
> long-winded
> case statement?
> Something like this would be nice (but I don't think such a syntax exists,
> at least not that I can find):
> SELECT col1, col2, col3 FROM mytable
> WHERE key='A'
> DEFAULT (0,'No','')
>
>
> -----Original Message-----
> From: [hidden email]
> <http://user/SendEmail.jtp?type=node&node=5815879&i=0>
> [mailto:[hidden email]
> <http://user/SendEmail.jtp?type=node&node=5815879&i=1>] On Behalf Of
> David G Johnston
> Sent: 22 August 2014 15:07
> To: [hidden email] <http://user/SendEmail.jtp?type=node&node=5815879&i=2>
> Subject: Re: [NOVICE] How to return a default value if no result
>
> Rob Northcott wrote
> > Is there a nice way of forcing a default result somehow?
>
> The Coalesce function is your friend:
>
> SELECT COALESCE( (SELECT subquery), <default> )
>
> David J.
>
>
>
> --
> View this message in context:
>
> http://postgresql.1045698.n5.nabble.com/How-to-return-a-default-value-if-no-
> result-tp5815850p5815860.html
> Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-novice mailing list ([hidden email]
> <http://user/SendEmail.jtp?type=node&node=5815879&i=3>) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>
>
>
>
> --
> Sent via pgsql-novice mailing list ([hidden email]
> <http://user/SendEmail.jtp?type=node&node=5815879&i=4>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>
>
> ------------------------------
> If you reply to this email, your message will be added to the discussion
> below:
>
> http://postgresql.1045698.n5.nabble.com/How-to-return-a-default-value-if-no-result-tp5815850p5815879.html
> To unsubscribe from How to return a default value if no result, click
> here
> <http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5815850&code=ZGF2aWQuZy5qb2huc3RvbkBnbWFpbC5jb218NTgxNTg1MHwtMzI2NTA0MzIx>
> .
> NAML
> <http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-return-a-default-value-if-no-result-tp5815850p5815883.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.