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.

You can't currently do this in PL/PgSQL. I believe this is scheduled
for a fix in 8.2, but for now, use another PL like PL/Perl, or (if
you're brave ;) C.

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

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

hi tomas,
The solution you sent worked, but i have another rissue related to this.

I am trying to create a stored procedure that can accept TableName,
ColumnName as parameter and return teh records pertaining to them.
This way, i will not need to make the procedures for every table.
But the problem is, I do not know the return type (number, name and type of
columns).

create or replace function ptest_Sort_Select(varchar, varchar) returns setof
RECORD as $$
DECLARE
res RECORD;
BEGIN
for res in
execute 'select * from ' || $1 || ' order by ' || $2 || ' asc'
loop
return next res;
end loop;
return;
END;
$$ language plpgsql strict;

I know cursors could help, but I need to use stored procedures, so as to
avoid open statements (outside procedure) like
BEGIN;
select select test_select('funcur','ptest','c');
fetch all in funcur;
COMMIT;