I have been working on this project which has MSSQL as DB provider, but due to some movements, my client wants this Database shifted from MSSQL to Postgres.
I am new to Postgres and today I found that in order to return table data I need to write functions instead of SPs with certain syntax different from MSSQL
I have some procedures in MSSQL which has around 80 columns.
It is getting tedius for me to write: returns table(col1 col1_type, ....80 times) statement (syntax found in docs) for all procedures.
EDIT: Also the most of the procedures are not returning exact columns as any table I have in my DB, so I believe I cannot use returns setof tablename.
I want to know if there is any other way to convert these procedures where I can skip this returns table statement. Or any offline converter which can do it for me?
Attaching dummy sp to understand how I am writing psql functions:
DROP function if exists GetUsers();
CREATE or replace function GetUsers()
returns table(Id varchar, Username varchar, Email varchar, Role varchar)
language 'plpgsql'
as $$
declare
BEGIN
return QUERY SELECT u.Id, u.Username, u.Email, ur."Role"
FROM Users u
JOIN UserRoleMapping urm
ON u.Id = urm.Id
JOIN UserRole ur
ON urm.RoleId = ur.Id;
end;$$;
select * from GetUsers();
I am using PostgreSQL 13.0.