I am trying to create a stored procedure that takes a column name as parameter and then uses it to sort the table for result set.
create or replace function ptest_Sort_Select(varchar) returns setof ptest1 as $$
DECLARE
res ptest1%ROWTYPE;
BEGIN
for res in
select * from ptest1 order by ptest1.$1 asc
loop
return next res;
end loop;
return;
END;
$$ language plpgsql strict;
but the output was not sorted.
Then i tried this stored procedure:
create or replace function test(varchar) returns int as $$
DECLARE
res int;
BEGIN
res:=0;
raise notice 'select * from ptest1 order by $1 asc';
return res;
END;
$$ language plpgsql strict;
db=# \d ptest1
Table "public.ptest1"
Column | Type | Modifiers
--------+-------------------+-----------
a | integer |
b | character varying |
c | double precision |
db=# select * from test('b');
NOTICE: select * from ptest1 order by $1 asc
test
------
0
(1 row)
Looks like $1 is not being translated.
Where am i going wrong?
vish
On 9/28/05, Tom Lane <[EMAIL PROTECTED]> wrote:
"Roy Wood" <[EMAIL PROTECTED]> writes:
> Description: ERROR: RETURN cannot have a parameter in function
> returning void at or near "NULL" at character 148
> Obtained this error creating a Function which contained 'RETURN NULL;' on
> the new 8.1-beta2
The complaint seems valid to me...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
