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
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? |