Re: How to declare return type for a function returning several rows and columns?

[Dmitriy Igrishin <dmitigr@xxxxxxxxx>]

Hey Alexander,

2012/6/12 Alexander Farber <alexander.farber@xxxxxxxxx>

Hello,

I'm trying to create the following function which gives me
a runtime error, because it obviously doesn't return a mere
integer but several rows and columns (result of a join):

# create or replace function pref_daily_misere() returns setof integer as $BODY$
       begin
           create temporary table temp_ids (id varchar not null) on
commit drop;
           insert into temp_ids (id)
                   select id
                   from pref_money
                   where yw = to_char(current_timestamp - interval '1
week', 'IYYY-IW')
                   order by money
                   desc limit 10;
           create temporary table temp_rids (rid integer not null) on
commit drop;
           insert into temp_rids (rid)
                   select rid
                   from pref_cards
                   where id in (select id from temp_ids) and
                   bid = 'Мизер' and
                   trix > 0;
           -- return query select rid from temp_rids;

           return query SELECT r.rid, r.cards, to_char(r.stamp,
'DD.MM.YYYY HH24:MI') as day,
                   c2.bid, c2.trix, c2.pos, c2.money, c2.last_ip, c2.quit,
                   u.id, u.first_name, u.avatar, u.female, u.city,
u.vip > CURRENT_DATE as vip
                   FROM pref_rounds r
                   JOIN pref_cards c1 USING (rid)
                   JOIN pref_cards c2 USING (rid)
                   JOIN pref_users u ON u.id = c2.id
                   WHERE r.rid in (select rid from temp_rids) order
by rid, pos;
           return;
       end;
       $BODY$ language plpgsql;

The runtime error in PostgreSQL 8.4.11 is:

# select pref_daily_misere();
ERROR:  structure of query does not match function result type
DETAIL:  Number of returned columns (15) does not match expected
column count (1).
CONTEXT:  PL/pgSQL function "pref_daily_misere" line 18 at RETURN QUERY

Does anybody please have an advice here?

You can create the view with your query:
SELECT r.rid, r.cards, to_char(r.stamp,
'DD.MM.YYYY HH24:MI') as day,
                   c2.bid, c2.trix, c2.pos, c2.money, c2.last_ip, c2.quit,
                   u.id, u.first_name, u.avatar, u.female, u.city,
u.vip > CURRENT_DATE as vip
                   FROM pref_rounds r
                   JOIN pref_cards c1 USING (rid)
                   JOIN pref_cards c2 USING (rid)
                   JOIN pref_users u ON u.id = c2.id;

and use this view both as the function return type and for
selecting inside the function.

-- 
// Dmitriy.