CODEWITHSUNDEEP
sqlpostgresql
user2793872
user2793872

Reputation: 319

Retrieving more than one value from the function

TableA

Id   imge
--   ----
1    1.jpeg
2    2.jpeg
1    1.jpeg
1    1.jpeg

o/p needed

id     image
------------
1      1.jpeg
       1.jpeg
       1.jpeg

I created a function,

create or replace function(vid in integer,vimg out varchar) returns setof record as
$$
declare
im varchar;
 begin
 select image into im from tablea wher id=$1;
 return query
 select im;
 $$
 end;
 plpgsql

But it's not working. I need to retrieve the images without using the arrays and loops.

Upvotes: 2

Views: 76

Answers (3)

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

You might be looking for GROUP_CONCAT()

SELECT GROUP_CONCAT(imge) as images
FROM TableA GROUP BY Id;

Oh I missed. You were in PostgreSQL, huh? No worries. There is an equivalent for group_concat in PostgreSQL: array_agg

SELECT id, array_agg(imge)
FROM TableA GROUP BY Id;

Upvotes: 1

roman
roman

Reputation: 117380

I think simple function like this is better to write in language sql instead of plpgsql:

create or replace function func(vid in integer)
returns table(vimg varchar)
as
$$
    select imge from tablea where id=$1;
$$ language sql;

Anyway, to return multiple records from function your can return either table or setof record.

sql fiddle demo

Upvotes: 1

kworr
kworr

Reputation: 3674

You are declaring your function as setof record meaning that it will return any number of rows spanning. You need to redeclare the function and change internal select's to match returning type.

Or I'm wrong and I just miss what you are trying to do.

Upvotes: 1

Related Questions