On Wed, Sep 28, 2005 at 12:25:54PM -0700, vishal saberwal wrote:
> hi,
>=20
> 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.
>=20
> 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 you might try
...
for res in execute 'select * from ptest1 order by ' || $1 || ' asc'
loop
...
i.e. build up the query string and run with execute.
> but the output was not sorted.
>=20
> 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';
I don't think plpgsql expands variables within strings. You'll have
to concatenate yourself, like so:
raise notice 'select * from ptest1 order by ' || $1 || ' asc';
HTH
-- tomas
