POSTGRES - Order By Case When Then End

Databases
1

Hi all,

I am having problems with CASE and ordering my results.
I am runnig this function to return all the records ordered by my column and ASC or DESC.

This is how I am calling the function.

BEGIN; SELECT dimensionsoftypes_read_all('funccursor',0,1); FETCH ALL IN funccursor; COMMIT;

This is the function. If you uncomment the first case and comment the second one it will work fine, something is wrong with the ASC or DESC.



--++++++++++++++++++++++++++++++++++++++++++++
-- Return all dimensions of types
--++++++++++++++++++++++++++++++++++++++++++++
DROP FUNCTION dimensionsoftypes_read_all (refcursor, integer,integer);
CREATE FUNCTION dimensionsoftypes_read_all (refcursor, integer,integer) RETURNS refcursor AS '
DECLARE
    myColumnToOrderBy ALIAS FOR $2;
    myOrder ALIAS FOR $3;
BEGIN

        OPEN $1 FOR
        SELECT   D.id, D.name, FT.name AS furnituretype
        FROM    dimensions D
        INNER JOIN   furnituretype FT ON   D.furnituretypeid=FT.id
        ORDER BY
--         CASE
--             WHEN myColumnToOrderBy=''0'' THEN cast(D.name as varchar(12))
--             WHEN myColumnToOrderBy=''1'' THEN cast(FT.name as varchar(12))
--             ELSE cast(D.id as varchar(12))
--         END;
        CASE
            WHEN myOrder=''0'' THEN ''asc''
            WHEN myOrder=''1'' THEN ''desc''
            ELSE ''desc''
        END;

        RETURN $1;
END;
' LANGUAGE 'plpgsql';

I hope you see what I am doing wrong.

Thanks in advance.

Erion

2 

SELECT   D.id, D.name, FT.name AS furnituretype
        FROM    dimensions D
        INNER JOIN   furnituretype FT ON   D.furnituretypeid=FT.id
        ORDER BY  D.name
        CASE
            WHEN myorder = ''0'' THEN
            CASE
                WHEN myColumnToOrderBy=''0'' THEN cast(D.name as varchar(12))
                WHEN myColumnToOrderBy=''1'' THEN cast(FT.name as varchar(12))
                ELSE cast(D.id as varchar(12))
            END
            ELSE ''
        END asc,
        CASE
            WHEN myorder = ''0'' THEN
            ''
            ELSE
            CASE
                WHEN myColumnToOrderBy=''0'' THEN cast(D.name as varchar(12))
                WHEN myColumnToOrderBy=''1'' THEN cast(FT.name as varchar(12))
                ELSE cast(D.id as varchar(12))
            END
        END desc;

asc/desc are not expressions they are qualifiers.

3

asc/desc are not expressions they are qualifiers.

This makes sense now,

thanks buddy,

Erion

4

This is my final working function for anyone like me that was trying to do the paging of records. I always do the paging and every time I call all the records it’s nice not to overload the transfer.

I am passing what to order by, asc or desc, and how many records per page and what page you are at from PHP.

I just thought this can be helpful to people.

Enjoy !


--++++++++++++++++++++++++++++++++++++++++++++
-- Return all dimensions of types
--++++++++++++++++++++++++++++++++++++++++++++
DROP FUNCTION dimensionsoftypes_read_all (refcursor, integer,integer,integer,integer);
CREATE FUNCTION dimensionsoftypes_read_all (refcursor, integer,integer,integer,integer) RETURNS refcursor AS '
DECLARE
    myColumnToOrderBy ALIAS FOR $2;
    myOrder  ALIAS FOR $3;
    myLimit    ALIAS FOR $4;
    myOffset  ALIAS FOR $5;
BEGIN

        OPEN $1 FOR
        SELECT   D.id, D.name, FT.name AS furnituretype
        FROM    dimensions D
        INNER JOIN   furnituretype FT ON   D.furnituretypeid=FT.id
        ORDER BY
        CASE
            WHEN myorder= ''1'' THEN
                CASE
                    WHEN myColumnToOrderBy=''0'' THEN cast(D.name as varchar(12))
                    WHEN myColumnToOrderBy=''1'' THEN cast(FT.name as varchar(12))
                    ELSE cast(D.id as varchar(12))
                END
        END desc,
        CASE
            WHEN myorder= ''0'' THEN
                CASE
                    WHEN myColumnToOrderBy=''0'' THEN cast(D.name as varchar(12))
                    WHEN myColumnToOrderBy=''1'' THEN cast(FT.name as varchar(12))
                    ELSE cast(D.id as varchar(12))
                END
        END asc
        LIMIT myLimit
        OFFSET myOffset;

        RETURN $1;
END;
' LANGUAGE 'plpgsql';