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

Re: Column Name parameter problem

From: tomas(at)tuxteam(dot)de (Tomas Zerolo)
To: vishal saberwal <vishalsaberwal(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Column Name parameter problem
Date: 2005-09-29 05:52:26
Message-ID: 20050929055226.GB15654@www.trapp.net (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: AndrewDate: 2005-09-29 08:27:44
Subject: BUG #1920: Installer no WIN1252 & UTF8 selection
Previous:From: Tom LaneDate: 2005-09-28 21:23:23
Subject: Re: BUG #1916: selection criteria from one outer join on clause applied to other joins

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