I have a function that returns a table but I need it to be a view, is it possible?
here’s the code:
-- Function: notes_to_financial_statements_for_the_month(character varying)
-- DROP FUNCTION notes_to_financial_statements_for_the_month(character varying);
CREATE OR REPLACE FUNCTION notes_to_financial_statements_for_the_month(IN _year_month character varying)
RETURNS TABLE(account_code character varying, amount numeric) AS
$BODY$
DECLARE
BEGIN
RETURN QUERY SELECT x.account_code,
--coa.acct_title,
--coa.level,
CASE WHEN x.account_code ~~ '1%' THEN
SUM(x.debit) - SUM(x.credit)
ELSE SUM(x.credit) - SUM(x.debit) END AS amount
FROM
(
select gl4_account_code AS account_code,
CASE WHEN gl4_entry_type = 'D' THEN
SUM(gl4_amount)
ELSE 0 END AS debit,
CASE WHEN gl4_entry_type = 'C' THEN
SUM(gl4_amount)
ELSE 0 END AS credit,
gl4_entry_type
from coop_fin_gl_level4
where gl4_posting_date::text ~ _year_month
and (gl4_account_code ~~ '1%'
or gl4_account_code ~~ '2%'
or gl4_account_code ~~ '3%')
group by gl4_account_code,
gl4_entry_type
UNION ALL
select gl5_account_code AS account_code,
CASE WHEN gl5_entry_type = 'D' THEN
SUM(gl5_amount)
ELSE 0 END AS debit,
CASE WHEN gl5_entry_type = 'C' THEN
SUM(gl5_amount)
ELSE 0 END AS credit,
gl5_entry_type
from coop_fin_gl_level5
where gl5_posting_date::text ~ _year_month
and (gl5_account_code ~~ '1%'
or gl5_account_code ~~ '2%'
or gl5_account_code ~~ '3%')
group by gl5_account_code,
gl5_entry_type
) x
JOIN coop_fin_chart_of_accounts coa
ON coa.account_code = x.account_code
GROUP BY x.account_code
ORDER BY x.account_code;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION notes_to_financial_statements_for_the_month(character varying)
OWNER TO postgres;