I am supposed to migrate a stored procedure from SQL Server to Postgres.
Here is the code of the procedure in SQL Server:
ALTER proc [dbo].[_GetUsers]
(
@tblUsersSelected typParameter READONLY
)
as
SELECT
Users.*
FROM
Users
JOIN
@tblUsersSelected UsersSelected
ON
Users.iUserID = UsersSelected.IntValue;
I am trying to achieve something similar to the "table valued parameter" in Postgres but failed. Can someone help please?
Use an array of integers:
CREATE FUNCTION getusers(p_userids integer[]) RETURNS SETOF users
LANGUAGE sql AS
'SELECT * FROM users WHERE iuserid = ANY (p_userids)';
Create it as a postgres function and then simply join it to your table of data.
Here's an example:
CREATE TEMPORARY TABLE _users
(
user_id serial
, user_name varchar
);
INSERT INTO _users
(user_name)
VALUES
('aaa')
,('beb')
,('caa')
,('fff');
CREATE FUNCTION pg_temp._test_table_values (_user_id int)
RETURNS TABLE
(
user_id int
,user_name varchar
)
SECURITY DEFINER AS $$
SELECT user_id, user_name
FROM _users
WHERE user_name LIKE '%a%'
AND user_id = _user_id;
$$ LANGUAGE SQL;
--join table and function
SELECT val.*
FROM _users AS u
JOIN pg_temp._test_table_values(u.user_id) AS val (user_id, user_name)
ON u.user_id = val.user_id;
It took me a while to get used to using functions for everything when I moved from SQL Server to PostgreSQL, good luck!
