Re: [HACKERS] polymorphic SQL functions has a problem with domains

2014-04-07 Thread Pavel Stehule
2014-04-02 17:19 GMT+02:00 Tom Lane :

> Pavel Stehule  writes:
> > I was informed about impossibility to use a polymorphic functions
> together
> > with domain types
>
> > see
>
> >  create domain xx as numeric(15);
>
> > create or replace function g(anyelement, anyelement)
> > returns anyelement as
> > $$  select $1 + $2 $$
> > language sql immutable;
>
> > postgres=# select g(1::xx, 2::xx);
> > ERROR:  return type mismatch in function declared to return xx
> > DETAIL:  Actual return type is numeric.
> > CONTEXT:  SQL function "g" during inlining
>
> That example doesn't say you can't use polymorphic functions with domains.
> It says that this particular polymorphic function definition is wrong:
> it is not making sure its result is of the expected data type.  I don't
> recall right now whether SQL functions will apply an implicit cast on the
> result for you, but even if they do, an upcast from numeric to some domain
> over numeric wouldn't be implicit.
>

I though about this issue again, and I am thinking so it is PostgreSQL bug

we can do safe transformation from Parent type -> domain.

and returning result require same transformation (in this case) - so
enforcing casting (not only binary casting) should be safe.

Otherwise - CAST(var AS var) should be useful and can helps too.

Regards

Pavel Stehule


>
> regards, tom lane
>


Re: [HACKERS] polymorphic SQL functions has a problem with domains

2014-04-02 Thread Pavel Stehule
2014-04-02 18:34 GMT+02:00 David Johnston :

> Tom Lane-2 wrote
> > David Johnston <
>
> > polobo@
>
> > > writes:
> >> Does something like:
> >> SELECT ($1 + $2)::$1%TYPE
> >> exist where you can explicitly cast to the type of the input argument?
> >
> > I don't think SQL-language functions have such a notation, but it's
> > possible in plpgsql, if memory serves.
>
> Indeed.
>
>
> http://www.postgresql.org/docs/9.3/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-TYPE
>

This casting is indirect via assignment




>
> Section 40.3.3
>
> You lose inlining but at least it (should) work.
>
> David J.
>
>
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/polymorphic-SQL-functions-has-a-problem-with-domains-tp5798349p5798367.html
> Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>


Re: [HACKERS] polymorphic SQL functions has a problem with domains

2014-04-02 Thread David Johnston
Tom Lane-2 wrote
> David Johnston <

> polobo@

> > writes:
>> Does something like:
>> SELECT ($1 + $2)::$1%TYPE 
>> exist where you can explicitly cast to the type of the input argument?
> 
> I don't think SQL-language functions have such a notation, but it's
> possible in plpgsql, if memory serves.

Indeed.

http://www.postgresql.org/docs/9.3/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-TYPE

Section 40.3.3

You lose inlining but at least it (should) work.

David J.






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/polymorphic-SQL-functions-has-a-problem-with-domains-tp5798349p5798367.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] polymorphic SQL functions has a problem with domains

2014-04-02 Thread Andres Freund
On 2014-04-02 12:27:30 -0400, Tom Lane wrote:
> David Johnston  writes:
> > Does something like:
> > SELECT ($1 + $2)::$1%TYPE 
> > exist where you can explicitly cast to the type of the input argument?
> 
> I don't think SQL-language functions have such a notation, but it's
> possible in plpgsql, if memory serves.

Sometimes you can play nasty tricks using COALESCE() to force a cast
like that.
E.g. SELECT COALESCE(NULLIF($1, $1), $1 + $2);

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] polymorphic SQL functions has a problem with domains

2014-04-02 Thread Pavel Stehule
2014-04-02 18:27 GMT+02:00 Tom Lane :

> David Johnston  writes:
> > Does something like:
> > SELECT ($1 + $2)::$1%TYPE
> > exist where you can explicitly cast to the type of the input argument?
>
> I don't think SQL-language functions have such a notation, but it's
> possible in plpgsql, if memory serves.
>
>
No, this possibility doesn't there, what I know.

but you can do assignment to some output variable - what is effective same

Pavel


> regards, tom lane
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>


Re: [HACKERS] polymorphic SQL functions has a problem with domains

2014-04-02 Thread Tom Lane
David Johnston  writes:
> Does something like:
> SELECT ($1 + $2)::$1%TYPE 
> exist where you can explicitly cast to the type of the input argument?

I don't think SQL-language functions have such a notation, but it's
possible in plpgsql, if memory serves.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] polymorphic SQL functions has a problem with domains

2014-04-02 Thread David Johnston
Tom Lane-2 wrote
> Pavel Stehule <

> pavel.stehule@

> > writes:
>> I was informed about impossibility to use a polymorphic functions
>> together
>> with domain types
> 
>> see
> 
>>  create domain xx as numeric(15);
> 
>> create or replace function g(anyelement, anyelement)
>> returns anyelement as
>> $$  select $1 + $2 $$
>> language sql immutable;
> 
>> postgres=# select g(1::xx, 2::xx);
>> ERROR:  return type mismatch in function declared to return xx
>> DETAIL:  Actual return type is numeric.
>> CONTEXT:  SQL function "g" during inlining
> 
> That example doesn't say you can't use polymorphic functions with domains.
> It says that this particular polymorphic function definition is wrong:
> it is not making sure its result is of the expected data type.  I don't
> recall right now whether SQL functions will apply an implicit cast on the
> result for you, but even if they do, an upcast from numeric to some domain
> over numeric wouldn't be implicit.

How would that be possible though?  Since any number of domains could be
defined over numeric as soon as the "+" operator causes the domain to be
lost there is no way to get it back manually - you cannot just make it
"SELECT ($1 + $2)::xx".

Does something like:

SELECT ($1 + $2)::$1%TYPE 

exist where you can explicitly cast to the type of the input argument?

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/polymorphic-SQL-functions-has-a-problem-with-domains-tp5798349p5798356.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] polymorphic SQL functions has a problem with domains

2014-04-02 Thread Pavel Stehule
2014-04-02 17:19 GMT+02:00 Tom Lane :

> Pavel Stehule  writes:
> > I was informed about impossibility to use a polymorphic functions
> together
> > with domain types
>
> > see
>
> >  create domain xx as numeric(15);
>
> > create or replace function g(anyelement, anyelement)
> > returns anyelement as
> > $$  select $1 + $2 $$
> > language sql immutable;
>
> > postgres=# select g(1::xx, 2::xx);
> > ERROR:  return type mismatch in function declared to return xx
> > DETAIL:  Actual return type is numeric.
> > CONTEXT:  SQL function "g" during inlining
>
> That example doesn't say you can't use polymorphic functions with domains.
> It says that this particular polymorphic function definition is wrong:
> it is not making sure its result is of the expected data type.  I don't
> recall right now whether SQL functions will apply an implicit cast on the
> result for you, but even if they do, an upcast from numeric to some domain
> over numeric wouldn't be implicit.
>

But I am not able to enforce a casting in polymorphic function

or there is some possibility?

Regards

Pavel


>
> regards, tom lane
>


Re: [HACKERS] polymorphic SQL functions has a problem with domains

2014-04-02 Thread Tom Lane
Pavel Stehule  writes:
> I was informed about impossibility to use a polymorphic functions together
> with domain types

> see

>  create domain xx as numeric(15);

> create or replace function g(anyelement, anyelement)
> returns anyelement as
> $$  select $1 + $2 $$
> language sql immutable;

> postgres=# select g(1::xx, 2::xx);
> ERROR:  return type mismatch in function declared to return xx
> DETAIL:  Actual return type is numeric.
> CONTEXT:  SQL function "g" during inlining

That example doesn't say you can't use polymorphic functions with domains.
It says that this particular polymorphic function definition is wrong:
it is not making sure its result is of the expected data type.  I don't
recall right now whether SQL functions will apply an implicit cast on the
result for you, but even if they do, an upcast from numeric to some domain
over numeric wouldn't be implicit.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] polymorphic SQL functions has a problem with domains

2014-04-02 Thread Pavel Stehule
Hello

I was informed about impossibility to use a polymorphic functions together
with domain types

see

 create domain xx as numeric(15);

create or replace function g(anyelement, anyelement)
returns anyelement as
$$  select $1 + $2 $$
language sql immutable;

postgres=# select g(1::xx, 2::xx);
ERROR:  return type mismatch in function declared to return xx
DETAIL:  Actual return type is numeric.
CONTEXT:  SQL function "g" during inlining

is this bug?

Regards

Pavel Stehule