Skip site navigation (1) Skip section navigation (2)

Column Name parameter problem

From: vishal saberwal <vishalsaberwal(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Column Name parameter problem
Date: 2005-09-28 19:25:54
Message-ID: 3e74dc25050928122564926d15@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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:=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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Roy Wood" <roy(dot)wood(at)clearswift(dot)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
>

Responses

pgsql-bugs by date

Next:From: David FetterDate: 2005-09-28 19:48:27
Subject: Re: Column Name parameter problem
Previous:From: Chris BrowneDate: 2005-09-28 15:32:04
Subject: Re: [BUGS] Postgresql problem in AIX 5.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group