How get data from loop in sql function

Question

I need get data as text and return as array from postgresql function. I have a clips media table with files and for each clip id i wanna get all files from media clips. I created function like this:

CREATE OR REPLACE FUNCTION clips_media_array(INT)
RETURNS text[] AS $$
  DECLARE
    r clips_media%ROWTYPE;
    t text[];
  BEGIN
  FOR r IN
    SELECT file_name
    FROM clips_media WHERE id_clips = $1 AND file_type = 2
  LOOP
    t := t.file_name;
  END LOOP;

  END;
$$
LANGUAGE plpgsql;

and i have an error: ERROR: invalid input syntax for integer: "bfeO4RbZ5R1CUT8.jpg" CONTEXT: PL/pgSQL function clips_media_array(integer) line 6 at FOR over SELECT rows

Could anyone help me with this ?

Your code doesn't match the error message (there is no over in your code). But you neither need a loop nor a function for this. A simple select array_agg(file_name) from clips_media where ... will do just fine — user330315
– user330315, Commented Dec 11, 2017 at 8:54

score 2 · Accepted Answer · 2017-12-11 09:01:41Z

You are overcomplicating things.

You don't need a loop - you don't even need a PL/pgSQL function.

You can put that into a simple SQL function with a single SQL query:

CREATE OR REPLACE FUNCTION clips_media_array(INT)
RETURNS text[] AS 
$$
  SELECT array_agg(file_name)
  FROM clips_media 
  WHERE id_clips = $1 
    AND file_type = 2;
$$
LANGUAGE sql;

Of course you can use that SQL query directly in your code without the overhead of a function.

If you want a PL/pgSQL function you would need to concat the values from the cursor variable inside the loop:

The following code is wrong:

LOOP
   t := t.file_name;
END LOOP;

and should be:

LOOP
  t := t || R.file_name;
END LOOP;

To reference the column from the loop record you need to use R.file_name and to add an element to an array you need to use ||.

You also need to initialize your result variable, otherwise it's null:

DECLARE
  r clips_media%ROWTYPE;
  t text[] := array[]::text[];

And finally you are missing a return statement:

  LOOP
    t := t || r.file_name;
  END LOOP;
  return t; --<< required to return something from a PL/pgSQL function
END;
$$

Collectives™ on Stack Overflow

How get data from loop in sql function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related