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")' ];