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

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

Collectives™ on Stack Overflow

postgresql function returns multiple columns

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related