Hi,
is it possible to use a parameter of a plpgsql-function to order a selection
inside the function?
What i would like to do is pass a column-name/alias-name to a plpgsql function
and use the variable as order-by-parameter like this:
create function foo(varchar)
RETURNS SETOF test
AS '
declare
rec test%ROWTYPE;
begin
FOR rec IN
SELECT *
FROM test
ORDER BY $1
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
end;
' LANGUAGE 'plpgsql';
This is only the core of what my function should do but can hopefully describe
what my question is about.
This function worked without error but did not sort anything.
I tried several solutions inside this function, e.g.:
SELECT *
FROM test
ORDER BY
CASE
WHEN $1 = ''foo'' THEN foo
WHEN $1 = ''bar'' THEN bar
END
wich throws the exception
"ERROR: CASE types character varying and integer cannot be matched"
where "foo" is of type varchar and "bar" is of type int4.
Since i read in the docu, that "ORDER BY" accepts the numer of the column to
sort by, i tried:
SELECT *
FROM test
ORDER BY
CASE
WHEN $1 = ''foo'' THEN 1
WHEN $1 = ''bar'' THEN 2
END
This worked without exception but did not sort either.
I tried another one:
SELECT
foo AS col1
bar AS col2
FROM test
ORDER BY
CASE
WHEN $1 = ''foo'' THEN col1
WHEN $1 = ''bar'' THEN col2
END
This throws
"ERROR: column "col1" does not exist.
Normal SQL-statements accept column-aliases as ORDER BY - criterium but inside
a CASE it does not seem to work.
It seems that with version 7.4 it is not possible to do what i tried.
Maybe some of the hackers want to put that on their list of feature requests
if they consider it a usefull feature.
It would be great to simply write it like this:
..... ORDER BY $1
I know that i can put the "ORDER BY" clause outside within the function call
(SELECT * FROM test('foo') ORDER BY foo) but since i want to use it together
with LIMIT AND OFFSET i don't want the function to return all rows and sort
it afterwards, because the count of rows returned would decrease the
functions performance enormously.
If someone knows a better solution i would be very gratefull.
Maybe a really have to build the statement as text and use it in a "FOR recIN
EXECUTE". I hoped to find a usefull workaround for that hack.
best regards, tom schön
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)
