Обсуждение: [GENERAL] How to convert MS SQL functions to pgSQL functions
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
Yogi Yang 007 schrieb am 31.12.2016 um 11:06: > 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 > Something like this: CREATE FUNCTION account_group_hierarchy(p_group_id numeric(18,0)) RETURNS TABLE (account_group_id NUMERIC(18,0)) AS $$ WITH recursive GroupInMainGroupP AS ( SELECT accountGroupId, 1 AS HierarchyLevel FROM dbo.tbl_AccountGroup WHERE (accountGroupId = p_group_id) UNION ALL SELECT e.accountGroupId, G.HierarchyLevel + 1 AS HierarchyLevel FROM dbo.tbl_AccountGroup AS e JOIN GroupInMainGroupP AS G ON e.groupUnder = G.accountGroupId ) select accountGroupId from GroupInMainGroupP; $$ language sql; Note that Postgres fold all unquoted identifiers to lowercase so it's usually better to use snake_case instead of CamelCase