From: | Thomas Schoen <t(dot)schoen(at)vitrado(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | ORDER BY with plpgsql parameter |
Date: | 2004-06-01 17:47:41 |
Message-ID: | 200406011947.41461.t.schoen@vitrado.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 rec IN
EXECUTE". I hoped to find a usefull workaround for that hack.
best regards, tom schön
From | Date | Subject | |
---|---|---|---|
Next Message | Scot L. Harris | 2004-06-01 17:50:16 | How to identify which column in a table is the primary key |
Previous Message | Campano, Troy | 2004-06-01 17:19:26 | Databases on separate filesystems |