Re: Column Name parameter problem

On Wed, Sep 28, 2005 at 12:25:54PM -0700, vishal saberwal wrote:
> 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 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.
>
> 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';

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