Casting composite types
От | Julian Scarfe |
---|---|
Тема | Casting composite types |
Дата | |
Msg-id | 007401c8ef55$972b11e0$6400a8c0@Wilbur обсуждение исходный текст |
Список | 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
В списке pgsql-general по дате отправления: