Casting composite types

From: "Julian Scarfe" <julian(at)avbrief(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Casting composite types
Date: 2008-07-26 19:27:03
Message-ID: 007401c8ef55$972b11e0$6400a8c0@Wilbur
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2008-07-26 19:37:17 Re: how to remove the duplicate elements from an array?
Previous Message Yi Zhao 2008-07-26 19:12:18 how to remove the duplicate elements from an array?