Postgres Pro
Downloads
Home   >   mailing lists

Thread: Return Record with CASE problem

Return Record with CASE problem

From
Rory Campbell-Lange
Date:
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>

Re: Return Record with CASE problem

From
Stephan Szabo
Date:
On Wed, 11 Jun 2003, Rory Campbell-Lange wrote:

> 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);

I think that should probably be typer varchar since that's what you seem
to actually be returning.

Re: Return Record with CASE problem

From
Rory Campbell-Lange
Date:
Thanks for the response, Stephan.

I ommitted to say that selecting AS typer varchar doesn't work either.

On 11/06/03, Stephan Szabo (sszabo@megazone23.bigpanda.com) wrote:
>
> On Wed, 11 Jun 2003, Rory Campbell-Lange wrote:
>
> > 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);
>
> I think that should probably be typer varchar since that's what you seem
> to actually be returning.
>
>

--
Rory and Barbara Campbell-Lange
Flat 10, 72 Tottenham Court Road, London, W1T 2HE
(m) (0)7939150693 (h) (0)2075805902 (f) (0)2076375880

Re: Return Record with CASE problem

From
Stephan Szabo
Date:
On Wed, 11 Jun 2003, Rory Campbell-Lange wrote:

> Thanks for the response, Stephan.
>
> I ommitted to say that selecting AS typer varchar doesn't work either.

Looks like it's getting typed as text actually, so try that (or explicitly
type the constants to varchar or text or whatever).

Re: Return Record with CASE problem

From
Dennis Gearon
Date:
What's this 'typer'? I see you guys using it, so it's NOT a misspell of 'type'?

Rory Campbell-Lange wrote:

> Thanks for the response, Stephan.
>
> I ommitted to say that selecting AS typer varchar doesn't work either.
>
> On 11/06/03, Stephan Szabo (sszabo@megazone23.bigpanda.com) wrote:
>
>>On Wed, 11 Jun 2003, Rory Campbell-Lange wrote:
>>
>>
>>>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);
>>
>>I think that should probably be typer varchar since that's what you seem
>>to actually be returning.
>>
>>
>
>

Re: Return Record with CASE problem

From
Stephan Szabo
Date:
On Wed, 11 Jun 2003, Dennis Gearon wrote:

> What's this 'typer'? I see you guys using it, so it's NOT a misspell of 'type'?

It's a "column" name for the output of the function return.

> Rory Campbell-Lange wrote:
>
> > Thanks for the response, Stephan.
> >
> > I ommitted to say that selecting AS typer varchar doesn't work either.
> >
> > On 11/06/03, Stephan Szabo (sszabo@megazone23.bigpanda.com) wrote:
> >
> >>On Wed, 11 Jun 2003, Rory Campbell-Lange wrote:
> >>
> >>
> >>>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);
> >>
> >>I think that should probably be typer varchar since that's what you seem
> >>to actually be returning.