postgresql function returns multiple columns

Question

I am confused about the passing process of the parameter from functions created in Postgres:

create type IncorrectRecord as (pattern_number integer, uoc_number integer);

create or replace function text1(pattern text, uoc_threshold integer) 
    returns  setof IncorrectRecord
as $$
begin
return next count(v1.code) as pattern_number, count(v2.code) as uoc_number
from (select * from q1_1 where code like pattern) as v1, (select
 * from q1_1 where code like pattern and uoc > uoc_threshold) as v2;
return;
end
$$ language plpgsql;

I have modified some, there is no parameters errors but it still does not work. when I tested it with

select * 
from test1('ECO%', 8)

error: function returns two columns.

Is there any thing wrong with the type? How can I fix it?.

Clodoaldo Neto · Accepted Answer · 2017-05-02 12:09:37Z

1

As the message says you are returning two columns. Return a composite type instead:

return next (
    count(v1.code) as pattern_number, count(v2.code) as uoc_number
)::IncorrectRecord

answered May 2, 2017 at 12:09

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

Collectives™ on Stack Overflow

postgresql function returns multiple columns

1 Answer 1

Comments

Your Answer

Collectives™ on Stack Overflow

postgresql function returns multiple columns

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest