sub hol_Suff {
my $self = shift;
my %parm = @_;
$parm{holid} or die;
my $dbh = $self->dbh;
my $sth = $dbh->prepare(
sprintf(
'SELECT public."hol_Suff"(%s)',
"$parm{holid}"
)
);
$sth->execute;
my ($actmask) = $sth->fetchrow_array;
return $actmask;
}
####
CREATE OR REPLACE FUNCTION public."mmgb_getAllBooks"(
IN parm_moneymgr character varying,
IN parm_sortorder character varying DEFAULT 'ASC'::character varying)
RETURNS TABLE(c1 bigint, c2 character varying, c3 character varying, c4 character varying, c5 character varying, c6 real, c7 timestamp without time zone) AS
$BODY$DECLARE
mmgr_flag boolean := NULL;
asc_flag boolean := true;
BEGIN
mmgr_flag = (parm_moneymgr IS NOT NULL);
asc_flag = (NOT parm_sortorder = 'DESC');
RETURN QUERY
SELECT b.mmgb_id,
i.instt_abbr,
i.instt_shortname,
c.cur_iso,
b.mmgb_shortname,
b.mmgb_weight,
b.mmgb_infodate
FROM mmgr_book b,
public."InstrumentType" i,
currency c,
public."MoneyMgr" m,
mmgr_status s
WHERE b.mmgb_insttid = i.instt_id
AND (m.mmgr_shortname = parm_moneymgr OR NOT mmgr_flag)
AND b.mmgb_curid = c.cur_id
AND s.mmgs_id = m.mmgr_statid
AND s.mmgs_shortname = 'Active'
AND m.mmgr_id = b.mmgb_mmgrid
ORDER BY asc_flag,
CASE WHEN asc_flag THEN
b.mmgb_infodate
END ASC,
CASE WHEN NOT asc_flag THEN
b.mmgb_infodate
END DESC;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION public."mmgb_getAllBooks"(character varying, character varying)
OWNER TO mydbo;
COMMENT ON FUNCTION public."mmgb_getAllBooks"(character varying, character varying) IS 'optional filters to cater for known use cases';
##
##
sub mmgb_getAllBooks {
my $self = shift;
my %parm = @_;
$parm{direction} ||= 'ASC';
my $dbh = $self->dbh;
my $sth = $dbh->prepare(
sprintf ('SELECT public."mmgb_getAllBooks"(%s, %s)',
$parm{mmgr_shortname} ? "character varying '$parm{mmgr_shortname}'"
: 'NULL',
"character varying '$parm{direction}'"
)
);
$sth->execute;
my @return;
while (my @row = $sth->fetchrow_array) {
push @return, @row; # got rid of \@row because each row is returned as a single string it seems
}
return \@return;
}
##
##
$VAR1 = [
'(17,SPX,"S&P 500 Index",USD,"S&P 500",50,"2017-06-15 15:06:47")',
'(19,FTSE100,"FTSE 100 Index",GBP,"FTSE 100",18,"2017-07-01 15:06:47")'
];