Thread: function with different return type depending on parameter?
is it possible to have a function that can return a different type depending on the parameters? Eg (approximately) if param = "one" then return 1 if param = "two" then return "2" if param = "three" then return 3.0 etc I can't see any variant type thanks James
Cheers
Rémi-C2014-02-12 10:20 GMT+01:00 James Harper <james.harper@bendigoit.com.au>:
is it possible to have a function that can return a different type depending on the parameters? Eg (approximately)
if param = "one" then return 1
if param = "two" then return "2"
if param = "three" then return 3.0
etc
I can't see any variant type
thanks
James
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello
no it is not possiblePavel
2014-02-12 10:20 GMT+01:00 James Harper <james.harper@bendigoit.com.au>:
is it possible to have a function that can return a different type depending on the parameters? Eg (approximately)
if param = "one" then return 1
if param = "two" then return "2"
if param = "three" then return 3.0
etc
I can't see any variant type
thanks
James
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
James Harper <james.harper@bendigoit.com.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 regards, tom lane
On Wed, Feb 12, 2014 at 9:24 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > James Harper <james.harper@bendigoit.com.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