PostgreSQL - Creating a loop with a SELECT

Question

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?

Hi @a_horse_with_no_name I wanted to append the result of the SELECT (which is a single record) to a variable in every loop. — JP Dolocanog
– JP Dolocanog, Commented Aug 15, 2018 at 14:35
But you declared the function as returns table so why are you appending everything to a single (non-existing) variable? — user330315
– user330315, Commented Aug 15, 2018 at 14:38

user330315 · Accepted Answer · 2018-08-15 14:37:58Z

1

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)

answered Aug 15, 2018 at 14:37

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

JP Dolocanog Over a year ago

I will try to implement this answer. If this answer solves the problem, I will indicate this as the correct answer.

Collectives™ on Stack Overflow

PostgreSQL - Creating a loop with a SELECT

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related