Re: Column Name parameter problem

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;

On 9/28/05, Tomas Zerolo <tomas(at)tuxteam(dot)de> wrote:
>
> 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
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.5 (GNU/Linux)
>
> iD8DBQFDO4EaBcgs9XrR2kYRAsy5AJ9J4a1cwY10mVriwvWVY/zdL30CKwCfWdgw
> rU3My3azyCCT8AG5iMIGXpk=
> =/8Tn
> -----END PGP SIGNATURE-----
>
>
>