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?.

If you want to retrieve the pair of values, where first one is the count where code like pattern and the second one is the count where code like pattern and uoc > uoc_threshold - you are using the wrong approach. Answering to your question, in short, it is impossible to use parameters in DDLs. — Abelisto
– Abelisto, Commented May 1, 2017 at 19:09
Is this some kind of homework? Looks awfully similar to: stackoverflow.com/questions/43735008/postgresql-functions — user330315
– user330315, Commented May 2, 2017 at 10:20

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

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related