Hello,
I am stuck while trying to convert/port a MS SQL server database to pgSQL.
Here is the code of the function in MS SQL server:
CREATE FUNCTION [dbo].[AccountGroupHierarchy]
-- Description: <Function to get AccountGroup Hierarchy for financial
statement>
(
@groupId numeric(18,0)
)
RETURNS @table_variable TABLE (accountGroupId NUMERIC(18,0))
AS
BEGIN
WITH GroupInMainGroupP AS (SELECT accountGroupId, 1 AS HierarchyLevel
FROM dbo.tbl_AccountGroup
WHERE (accountGroupId = @groupId)
UNION ALL
SELECT e.accountGroupId, G.HierarchyLevel + 1 AS HierarchyLevel
FROM dbo.tbl_AccountGroup AS e CROSS JOIN
GroupInMainGroupP AS G
WHERE (e.groupUnder = G.accountGroupId))
INSERT INTO @table_variable
(accountGroupId)
(
SELECT accountGroupId FROM GroupInMainGroupP)
Return
END
I need to convert this code to pgSQL.
Please help.
Thanks,
Yogi Yang