How to convert MS SQL functions to pgSQL functions

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general