Postgres Pro
Downloads
Home   >   mailing lists

Thread: Function accepting array of complex type

Function accepting array of complex type

From
Jim Nasby
Date:
This works:

CREATE TYPE c AS (r float, i float);
CREATE FUNCTION mag(c c) RETURNS float LANGUAGE sql AS $$
SELECT sqrt(c.r^2 + c.i^2)
$$;
SELECT mag( (2.2, 2.2) );       mag
------------------ 3.11126983722081

But this doesn't:
CREATE FUNCTION magsum( c c[] ) RETURNS float LANGUAGE sql AS $$
SELECT sum(sqrt(c.r^2 + c.i^2)) FROM unnest(c) c
$$;
SELECT magsum( array[row(2.1, 2.1), row(2.2,2.2)] );
ERROR:  function magsum(record[]) does not exist at character 8

Presumably we're playing some games with resolving (...) into a complex 
type instead of a raw record; what would be involved with making that 
work for an array of a complex type? I don't see anything array-specific 
in parse_func.c, so I'm not sure what the path for this is...
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

Re: Function accepting array of complex type

From
Tom Lane
Date:
Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
> This works:
> CREATE TYPE c AS (r float, i float);
> CREATE FUNCTION mag(c c) RETURNS float LANGUAGE sql AS $$
> SELECT sqrt(c.r^2 + c.i^2)
> $$;
> SELECT mag( (2.2, 2.2) );
>         mag
> ------------------
>   3.11126983722081

> But this doesn't:
> CREATE FUNCTION magsum( c c[] ) RETURNS float LANGUAGE sql AS $$
> SELECT sum(sqrt(c.r^2 + c.i^2)) FROM unnest(c) c
> $$;
> SELECT magsum( array[row(2.1, 2.1), row(2.2,2.2)] );
> ERROR:  function magsum(record[]) does not exist at character 8

You need to cast it to some specific record type:

regression=# SELECT magsum( array[row(2.1, 2.1), row(2.2,2.2)]::c[] );     magsum      
------------------6.08111831820431
(1 row)
        regards, tom lane

Re: Function accepting array of complex type

From
"David G. Johnston"
Date:
On Tue, Aug 25, 2015 at 6:21 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
This works:

CREATE TYPE c AS (r float, i float);
CREATE FUNCTION mag(c c) RETURNS float LANGUAGE sql AS $$
SELECT sqrt(c.r^2 + c.i^2)
$$;
SELECT mag( (2.2, 2.2) );
       mag
------------------
 3.11126983722081

But this doesn't:
CREATE FUNCTION magsum( c c[] ) RETURNS float LANGUAGE sql AS $$
SELECT sum(sqrt(c.r^2 + c.i^2)) FROM unnest(c) c
$$;
SELECT magsum( array[row(2.1, 2.1), row(2.2,2.2)] );
ERROR:  function magsum(record[]) does not exist at character 8

Presumably we're playing some games with resolving (...) into a complex type instead of a raw record; what would be involved with making that work for an array of a complex type? I don't see anything array-specific in parse_func.c, so I'm not sure what the path for this is...

​magsum( c c[] ) never gets a chance to coerce its argument because array[row(...), row(...)]​
 
​beats it to the punch.  SELECT mag( row(...) ) does see the untyped row and seeing only a single function with parameter "c" coerces it to match.​  I'm not sure what can be done besides adding the cast to either the array[]::c[] or to the individual items array[ row(...)::c ].

Hopefully the thought helps because I'm useless when it comes to the actual code.

This does seem similar to how non-array literals are treated; though I'm not sure if there are inferences (or node look-through) occurring in literals that make some cases like this work while the corresponding "unknown record" gets set in stone differently.

David J.​

Re: Function accepting array of complex type

From
Andrew Dunstan
Date:

On 08/25/2015 06:21 PM, Jim Nasby wrote:
> CREATE FUNCTION magsum( c c[] ) RETURNS float LANGUAGE sql AS $$
> SELECT sum(sqrt(c.r^2 + c.i^2)) FROM unnest(c) c
> $$;
> SELECT magsum( array[row(2.1, 2.1), row(2.2,2.2)] ); 


SELECT magsum( array[row(2.1, 2.1), row(2.2,2.2)]::c[] );

cheers

andrew

Re: Function accepting array of complex type

From
Jim Nasby
Date:
On 8/25/15 6:28 PM, Tom Lane wrote:
> Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
>> This works:
>> CREATE TYPE c AS (r float, i float);
>> CREATE FUNCTION mag(c c) RETURNS float LANGUAGE sql AS $$
>> SELECT sqrt(c.r^2 + c.i^2)
>> $$;
>> SELECT mag( (2.2, 2.2) );
>>          mag
>> ------------------
>>    3.11126983722081
>
>> But this doesn't:
>> CREATE FUNCTION magsum( c c[] ) RETURNS float LANGUAGE sql AS $$
>> SELECT sum(sqrt(c.r^2 + c.i^2)) FROM unnest(c) c
>> $$;
>> SELECT magsum( array[row(2.1, 2.1), row(2.2,2.2)] );
>> ERROR:  function magsum(record[]) does not exist at character 8
>
> You need to cast it to some specific record type:
>
> regression=# SELECT magsum( array[row(2.1, 2.1), row(2.2,2.2)]::c[] );

Right, I was wondering how hard it would be to improve that, but it's 
not clear to me where to look at in the code. Does the resolution happen 
as part of parsing, or is it further down the road?
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

Re: Function accepting array of complex type

From
Tom Lane
Date:
Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
> On 8/25/15 6:28 PM, Tom Lane wrote:
>> You need to cast it to some specific record type:
>> regression=# SELECT magsum( array[row(2.1, 2.1), row(2.2,2.2)]::c[] );

> Right, I was wondering how hard it would be to improve that, but it's 
> not clear to me where to look at in the code. Does the resolution happen 
> as part of parsing, or is it further down the road?

It would possibly make sense to allow coercion of record[] to
complex-array types, but there would be a lot of code to be written to
support it.  See the unimplemented cases referencing RECORDARRAYOID in
parse_coerce.c, and compare to corresponding cases for coercing RECORDOID
to complex.  (Note that the way array[...]::foo[] works is very specific
to ARRAY constructs, so it would not handle the general case.  OTOH,
coerce_record_to_complex doesn't pretend to handle all cases either.)
        regards, tom lane