Return Record with CASE problem

Поиск
Список
Период
Сортировка
От Rory Campbell-Lange
Тема Return Record with CASE problem
Дата
Msg-id 20030611135254.GA24986@campbell-lange.net
обсуждение исходный текст
Ответы Re: Return Record with CASE problem  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Список pgsql-general
Selecting on this function works fine when I don't use the CASE column
'typer' in my select and don't include it in the returned 'resulter' in
the function. 'typer'  is a int2, while the output is a varchar. How can
I get to "typer" in my select?

Failed select:

temporary=> SELECT
                *
            FROM
                fn_v1_board_view_board (1, 1)
            AS (n_id integer, t_description varchar, t_name varchar,
                typer integer, n_id_photo integer);

ERROR:  Query-specified return tuple and actual function return tuple do
not match

Function:

CREATE OR REPLACE FUNCTION fn_v1_board_view_board
    (integer, integer) RETURNS RECORD
    AS '
DECLARE
    boardid                ALIAS for $1;
    personid               ALIAS for $2;
    recone                 RECORD;
    resulter               RECORD;
BEGIN
    SELECT INTO recone
        n_id, t_description, t_name,
        CASE n_type WHEN 0 then ''personal''
                    WHEN 1 then ''private''
                    WHEN 2 then ''blog''
                    ELSE ''public''
        END as typer, n_id_photo
    FROM
        boards
    WHERE
        n_id = boardid;

    IF NOT FOUND THEN
        RAISE EXCEPTION ''board does not exist at fn_v1_board_view_board'';
        SELECT INTO resulter 0,0;
    END IF;

    SELECT INTO resulter
        recone.n_id, recone.t_description, recone.t_name, recone.typer,
        recone.n_id_photo;

    RETURN resulter;

END;'
    LANGUAGE plpgsql;


--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>

В списке pgsql-general по дате отправления:

Предыдущее
От: "Jimmie H. Apsey"
Дата:
Сообщение: Re: doing VALID UNTIL programmatically in SQL ?
Следующее
От: Rory Campbell-Lange
Дата:
Сообщение: Options for select from function returning record?