CODEWITHSUNDEEP
sqlpostgresqlstored-procedures
JP Dolocanog
JP Dolocanog

Reputation: 451

PostgreSQL - Creating a loop with a SELECT

I have these Stored Procedure logic to be implemented:

CREATE OR REPLACE FUNCTION get_array(arraynumbers integer[])
RETURNS TABLE (name text) AS $$
DECLARE 
index integer := 0
BEGIN 
FOREACH index < arraynumbers
LOOP
SELECT e.name as empname FROM employee as e
WHERE e.id = arraynumbers[index]
LIMIT 1
name.push(empname)
ENDLOOP;
RETURN name;
END;
$$
LANGUAGE PLPGSQL;

The goal is to loop based on the length of the array parameter and every index of the parameter will be the condition for retrieving a record and push it to a variable and return the variable as table.

What is the correct way of writing it in PostgreSQL Stored Procedure?

Upvotes: 0

Views: 72

Answers (1)

user330315
user330315

Reputation:

It's unclear to me what exactly the result should be, but as far as I can tell, you don't need a loop or a PL/pgSQL function:

CREATE OR REPLACE FUNCTION get_array(arraynumbers integer[])
  RETURNS TABLE (name text) 
AS 
$$
  SELECT e.name 
  FROM employee as e
  WHERE e.id = any(arraynumbers);
$$
LANGUAGE SQL;

This will return one row for each id in arraynumbers that exist in the employee table. As the function is declared as returns table there is no need to collect the values into a single variable (which you didn't declare to begin with)

Upvotes: 1

Related Questions