CODEWITHSUNDEEP
sql-serverpostgresqlcommon-table-expressionrecursive-query
Lionel Cichero
Lionel Cichero

Reputation: 563

Recursive CTE compatible in both SQL Server and PostgreSQL

I have a recursive CTE that works fine in SQL Server, I am trying to adapt the query to make it work in PostgreSQL, the requirement is that the same query works well in both SQL Server and PostgreSQL.

How can I convert this query sql-fiddle to a recursive query that will work on both databases?

The depth of hierarchy is variable, could be 1, 2, 3, or 50. I have tried doing a recursive query but my knowledge of SQL is very limited.

Schema SQL

create table IdentityTable (id int, name varchar(255));
insert into IdentityTable values (1, 'Lionel');
insert into IdentityTable values (2, 'Head Office User');
insert into IdentityTable values (3, 'Snapshot Access');
insert into IdentityTable values (4, 'Employee Administrator');
insert into IdentityTable values (5, 'Event Type Administrator');

create table IdentityUserGroupAsmt (id int, identityTable int, userGroup int);
insert into IdentityUserGroupAsmt values (1, 1, 2);
insert into IdentityUserGroupAsmt values (2, 2, 3);
insert into IdentityUserGroupAsmt values (3, 3, 4);
insert into IdentityUserGroupAsmt values (4, 4, 5);

Query SQL

with 
    cte as (    
        select it.name, it.id, iuga.userGroup from IdentityTable it
            join IdentityUserGroupAsmt iuga
                on it.id = iuga.identityTable
        where it.name = 'Lionel'
        
    union all

        select it.name, it.id, iuga.userGroup from IdentityUserGroupAsmt iuga
            join cte cte
                on cte.userGroup = iuga.identityTable
            join IdentityTable it
                on iuga.userGroup = it.id
    )

select * from cte

Upvotes: 1

Views: 459

Answers (1)

Piotr Palka
Piotr Palka

Reputation: 3169

You can't meet the requirement that:

the same query works well in both SQL Server and PostgreSQL.

In SQL Server you need to use regular CTE, in PostgreSQL you need to use CTE with special syntax WITH RECURSIVE.
Your application will have to detect the database engine and adjust the syntax accordingly.

Another option would be to build a dedicated Stored Procedure for each database.

Upvotes: 1

Related Questions