From: | Rory Campbell-Lange <rory(at)campbell-lange(dot)net> |
---|---|
To: | Postgresql General List <pgsql-general(at)postgresql(dot)org> |
Subject: | Return Record with CASE problem |
Date: | 2003-06-11 13:52:55 |
Message-ID: | 20030611135254.GA24986@campbell-lange.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)campbell-lange(dot)net>
<www.campbell-lange.net>
From | Date | Subject | |
---|---|---|---|
Next Message | Rory Campbell-Lange | 2003-06-11 13:56:16 | Options for select from function returning record? |
Previous Message | Jimmie H. Apsey | 2003-06-11 13:41:08 | Re: doing VALID UNTIL programmatically in SQL ? |