CODEWITHSUNDEEP
postgresqlstored-procedurestable-valued-parameters
Muhammad Waheed
Muhammad Waheed

Reputation: 1088

Passing table valued parameter in Postgres stored procedure

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?

Upvotes: 2

Views: 3690

Answers (2)

DatabaseShouter
DatabaseShouter

Reputation: 945

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!

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246083

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)';

Upvotes: 2

Related Questions