从函数中检索多个值

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.

但它不起作用。我需要在不使用数组和循环的情况下检索图像。

3 个解决方案

#1

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.

您正在将您的函数声明为setof记录,这意味着它将返回任意数量的行。您需要重新声明该函数并更改内部选择以匹配返回类型。

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

或者我错了,我只是想念你想要做的事情。

#2

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

我认为像这样的简单函数更好用sql而不是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.

无论如何,要从函数返回多个记录,您可以返回table或setof记录。

sql fiddle demo

sql小提琴演示

#3

You might be looking for GROUP_CONCAT()

您可能正在寻找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

哦,我错过了。你在PostgreSQL,对吧?别担心。 PostgreSQL中的group_concat有一个等价物:array_agg

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

#1