CODEWITHSUNDEEP
sqlpostgresqlplpgsql
John Keaton
John Keaton

Reputation: 51

Postgresql functions

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?

Upvotes: 0

Views: 657

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51629

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;

Upvotes: 1

Related Questions