From: | Thomas Kellerer <spam_eater(at)gmx(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How to convert MS SQL functions to pgSQL functions |
Date: | 2016-12-31 10:12:40 |
Message-ID: | o480ap$8th$1@blaine.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tim Uckun | 2016-12-31 10:49:33 | Re: Performance PLV8 vs PLPGSQL |
Previous Message | Yogi Yang 007 | 2016-12-31 10:06:44 | How to convert MS SQL functions to pgSQL functions |