Обсуждение: Column Name parameter problem

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:=3D0;
raise notice 'select * from ptest1 order by $1 asc';
return res;
END;
$$ language plpgsql strict;

db=3D# \d ptest1
Table "public.ptest1"
Column | Type | Modifiers
--------+-------------------+-----------
a | integer |
b | character varying |
c | double precision |


db=3D# 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@sss.pgh.pa.us> wrote:
>
> "Roy Wood" <roy.wood@clearswift.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
>

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,
>=20
> 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.
>=20
> 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.
>=20
> Then i tried this stored procedure:
> create or replace function test(varchar) returns int as $$
> DECLARE
> res int;
> BEGIN
> res:=3D0;
> 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;

On 9/28/05, Tomas Zerolo <tomas@tuxteam.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:=3D0;
> > 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=3D
> =3D/8Tn
> -----END PGP SIGNATURE-----
>
>
>