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

Hey Alexander,

2012/6/12 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>

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