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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
