1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
-- Perl Weekly Challenge 170
-- Task 2
create table if not exists a( a int, b int );
truncate table a;
insert into a values (1,2), (3,4);
create table if not exists b( a int, b int );
truncate table b;
insert into b values (5,6), (7,8);
CREATE SCHEMA IF NOT EXISTS pwc170;
CREATE OR REPLACE FUNCTION
pwc170.task2_plperl( text, text )
RETURNS TABLE( a int, b int, c int, d int )
AS $CODE$
my ( $table_a, $table_b ) = @_;
elog( DEBUG, "Reading tables $table_a and $table_b" );
my ( $rs_a, $rs_b );
$rs_a = spi_exec_query( "SELECT a,b FROM $table_a" );
$rs_b = spi_exec_query( "SELECT a,b FROM $table_b" );
for my $row_a ( 0 .. $rs_a->{ processed } - 1 ) {
elog( DEBUG, "Loop A $row_a out of " . $rs_a->{ processed } );
my ($aa, $ab) = ( $rs_a->{ rows }[ $row_a ]->{ a }, $rs_a->{ rows }[ $row_a ]->{ b } );
for my $row_b ( 0 .. $rs_b->{ processed } - 1 ) {
elog( DEBUG, "Loop B $row_b out of " . $rs_b->{ processed } );
my ($ba, $bb) = ( $rs_b->{ rows }[ $row_b ]->{ a }, $rs_b->{ rows }[ $row_b ]->{ b } );
elog( DEBUG, "Computing $aa $ab X* $ba $bb" );
my $result = {
a => $aa * $ba,
b => $aa * $bb,
c => $ab * $ba,
d => $ab * $bb,
};
return_next( $result );
}
}
return undef;
$CODE$
LANGUAGE plperl;
|
