Thread: Casting composite types

Casting composite types

From
"Julian Scarfe"
Date:
Using 8.1

# create table foo (a integer, b integer);
# create table baz (b integer, c integer);

# insert into foo values (8,9);
# insert into baz values (9,1);

# select * from foo;
 a | b
---+---
 8 | 9
(1 row)

# select * from baz;
 b | c
---+---
 9 | 1
(1 row)

# create view foobaz as select foo.*, baz.c from foo join baz using (b);

# select * from foobaz;
 a | b | c
---+---+---
 8 | 9 | 1
(1 row)

So far so good.

I have many functions that take the composite type foo, and therefore wish
to be able to cast a foobaz into a foo, by taking only columns in foo (i.e.
a and b).  But of course there's no cast defined:

# select foobaz::foo from foobaz;
ERROR:  cannot cast type foobaz to foo

# select foo(foobaz) from foobaz;
ERROR:  function foo(foobaz) does not exist
HINT:  No function matches the given name and argument types. You may need
to add explicit type casts.

Ideally, I'd love to avoid writing a separate function for each foo,baz pair
of types as I have many of each. In any case, I want to avoid specifying the
columns of foo in the code of foo(foobaz) so that the function doesn't break
when I alter the foo table.  The best I've got for the latter is:

CREATE OR REPLACE FUNCTION foo(foobaz)  RETURNS foo AS $$
my ($foobaz) = @_;
my $foo = {};
$row = spi_fetchrow(spi_query("SELECT * from foo limit 1"));
for (keys %$row) {$foo->{$_} = $foobaz->{$_}};
return $foo;
$$ LANGUAGE plperlu;

which feels very cumbersome, but works, provided foo is not empty.

# select foo(foobaz) from foobaz;
  foo
-------
 (8,9)
(1 row)

Am I missing an obvious trick or syntax here for such an 'autocast'?  Or
have I just been corrupted by Perl to take types too lightly?

Thanks

Julian