hi,
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:=3D0;
raise notice 'select * from ptest1 order by $1 asc';
return res;
END;
$$ language plpgsql strict;
db=3D# \d ptest1
Table "public.ptest1"
Column | Type | Modifiers
--------+-------------------+-----------
a | integer |
b | character varying |
c | double precision |
db=3D# 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 <tgl@sss.pgh.pa.us> wrote:
>
> "Roy Wood" <roy.wood@clearswift.com> 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
>
