Re: function with different return type depending on parameter?

On Wed, Feb 12, 2014 at 9:24 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> James Harper <james(dot)harper(at)bendigoit(dot)com(dot)au> writes:
>> is it possible to have a function that can return a different type
>> depending on the parameters?
>
> The data type of any expression (including a function call) has to be
> determinable at parse time, so no you can't just randomly return a
> run-time-determined data type.
>
> However, have you looked at the "polymorphic functions" feature?
> You can declare a function as returning the same data type that
> one of its inputs has. This seems to cover most of the cases
> that are useful in practice.
>
> http://www.postgresql.org/docs/9.3/static/extend-type-system.html#EXTEND-TYPES-POLYMORPHIC
> http://www.postgresql.org/docs/9.3/static/xfunc-sql.html#AEN52916

You can also define a function to return 'text', which by virtue of
every other type being able to be casted to/from text, can be used as
a kind of variant. This technique is pretty dubious mostly, but can
occasionally be used to work around problematic situations.

There's also hstore for dealing with record-variant situations (this
is especially useful in, say, auditing triggers), and it's emerging
strong contender: json. All of the text variant approaches though
simply defer the type resolution to some later point, which can lead
to performance and logical consistency issues if you're not careful.

merlin