Postgresql functions

Question

These are the type definitions provided to me

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

My current progress is:

create or replace function q1(pattern text, uoc_threshold integer)
  returns setof IR
  as $$
  BEGIN
    RETURN QUERY
    select count(code) from temp where code like $1;
    RETURN QUERY 
    select count(code) from temp where code like $1 and uoc > $2;

  END;
$$ language plpgsql;

My output needs to be like this : Query:-

select * 
from q1('ECO%', 6);

pattern_number  |   uoc_number 
80              |         5

I get an error saying:

ERROR: structure of query does not match function result type
DETAIL: Returned type bigint does not match expected type integer in column 1.
CONTEXT: PL/pgSQL function q1(text,integer) line 3 at RETURN QUERY

How do I fix this?

Your function returns two rows with a single (bigint) column. You want a single row with a single column that is a record. — user330315
– user330315, Commented May 2, 2017 at 10:01
do I get it right? you want select count(code) from temp where code like $1 as pattern_number and select count(code) from temp where code like $1 and uoc > $2 as uoc_number ?.. I added answer with code - sic? — Vao Tsun
– Vao Tsun, Commented May 2, 2017 at 10:04
@VaoTsun Yes. That is exactly what I want but your answer seems to not resolve the error — John Keaton
– John Keaton, Commented May 2, 2017 at 10:17

Vao Tsun · Accepted Answer · 2017-05-02 10:23:01Z

1

Is it what you want?..

create or replace function q1(pattern text, uoc_threshold integer)
  returns setof IR
  as $$
  BEGIN
    RETURN QUERY
    select (select count(code) from temp where code like $1)::integer
    ,(
    select count(code) from temp where code like $1 and uoc > $2)::integer;

  END;
$$ language plpgsql;

edited May 2, 2017 at 10:23

answered May 2, 2017 at 10:07

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

1 Comment

John Keaton Over a year ago

You got it right but the error is still there. It is the same exact error.

Collectives™ on Stack Overflow

Postgresql functions

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related