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

[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?

The background: I have a table holding card game rounds:

#  \d pref_rounds;
                                    Table "public.pref_rounds"
 Column |            Type             |                         Modifiers
--------+-----------------------------+-----------------------------------------------------------
 rid    | integer                     | not null default
nextval('pref_rounds_rid_seq'::regclass)
 cards  | text                        |
 stamp  | timestamp without time zone | default now()
Indexes:
    "pref_rounds_pkey" PRIMARY KEY, btree (rid)
Referenced by:
    TABLE "pref_cards" CONSTRAINT "pref_cards_rid_fkey" FOREIGN KEY
(rid) REFERENCES pref_rounds(rid) ON DELETE CASCADE

Each round is played by 3 players:

#  \d pref_cards;
               Table "public.pref_cards"
 Column  |            Type             |   Modifiers
---------+-----------------------------+---------------
 rid     | integer                     |
 id      | character varying(32)       |
 bid     | character varying(32)       | not null
 trix    | integer                     | not null
 pos     | integer                     | not null
 money   | integer                     | not null
 last_ip | inet                        |
 quit    | boolean                     |
 stamp   | timestamp without time zone | default now()
Indexes:
    "pref_cards_id_index" btree (id)
    "pref_cards_rid_index" btree (rid)
Foreign-key constraints:
    "pref_cards_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id) ON
DELETE CASCADE
    "pref_cards_rid_fkey" FOREIGN KEY (rid) REFERENCES
pref_rounds(rid) ON DELETE CASCADE

I'm trying to take the top 10 players of the last week,
so that I can display their certain bids for analyse on
a web page (similar to the table in the middle of
http://preferans.de/user.php?id=OK471018960997 )

Thank you
Alex

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general