[BUGS] Column Name parameter problem

#1

------=_Part_3301_21831597.1127935554593
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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 (AT) sss (DOT) pgh.pa.us> wrote:

Quote:

"Roy Wood" <roy.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

------=_Part_3301_21831597.1127935554593
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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 or=
der by $1 asc'; 
        return res; 
END; 
$$ language plpgsql strict; 
 
db=3D# \d ptest1 
       &nbsp ; Table "public.ptest1&=
quot; 
 Column |       Type   &n=
bsp;    | Modifiers 
--------+-------------------+----------- 
 a      | integer    &nbs=
p;      | 
 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 
 <div>On 9/28/05, Tom Lane <<a href=3D"mailto:tgl (AT) sss (DOT) pgh.pa.us">tgl (AT) sss (DOT) pgh.pa.us=
</a>> wrote:<blockquote class=3D"gmail_quote" style=3D"border-lef=
t: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1=
ex;">
"Roy Wood" <<a href=3D"mailto:roy.wood (AT) clearswift (DOT) com">roy.woo=
d (AT) clearswift (DOT) com</a>> writes: > Description:   &nbs=
p;    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 'RETUR=
N NULL;' on > the new 8.1-beta2 The complaint seems valid to m=
e...       &n bsp;   &nb=
sp;       &nbsp ;    =
 regards,
tom lane ---------------------------(end of broadcast)--------------=
------------- TIP 5: don't forget to increase your free space map settin=
gs </blockquote></div> 

------=_Part_3301_21831597.1127935554593--

#2

On Wed, Sep 28, 2005 at 12:25:54PM -0700, vishal saberwal wrote:

Quote:

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 (AT) fetter (DOT) org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

#3

--TakKZr9L6Hm6aLOc
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Wed, Sep 28, 2005 at 12:25:54PM -0700, vishal saberwal wrote:

Quote:

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.

Quote:

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

--TakKZr9L6Hm6aLOc
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (GNU/Linux)

iD8DBQFDO4EaBcgs9XrR2kYRAsy5AJ9J4a1cwY10mVriwvWVY/zdL30CKwCfWdgw
rU3My3azyCCT8AG5iMIGXpk=
=/8Tn
-----END PGP SIGNATURE-----

--TakKZr9L6Hm6aLOc--

#4

------=_Part_4887_1726058.1128038942730
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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 seto=
f
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:

Quote:

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-----

------=_Part_4887_1726058.1128038942730
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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

Quote:

br

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 seto=
f RECORD as $$ 
DECLARE 
    res RECORD; 
BEGIN 
    for res in 
        execute 'select * from ' || $1 || ' o=
rder 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 av=
oid open statements (outside procedure) like 
BEGIN; 
select select test_select('funcur','ptest','c'); 
fetch all in funcur; 
COMMIT; 
 <div>On 9/28/05, Tomas Zerolo <<a href=3D"mailto:tomas (AT) tuxteam (DOT) de">tomas (AT) tuxteam (DOT) =
de</a>> wrote:<blockquote class=3D"gmail_quote" style=3D"border-l=
eft: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left:=
1ex;">
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 col=
umn name as > parameter and then uses it to sort the table for result=
set.
 > > create or replace function ptest_Sort_Select(varchar) retu=
rns setof ptest1 > as $$ > DECLARE > res ptest1%ROWTYPE;<=
br>> BEGIN > for res in > select * from ptest1 order by ptes=
t1.$1 asc
 > loop > return next res; > end loop; > return;<br=

Quote:

END; > $$ language plpgsql strict; But you might try =
   ...    for res in execute 'select * from ptest1 o=

rder by ' || $1 || ' asc'
    loop    ... i.e. build up the query st=
ring and run with execute. > but the output was not sorted. &g=
t; > Then i tried this stored procedure: > create or replace fu=
nction test(varchar) returns int as $$
 > DECLARE > res int; > BEGIN > res:=3D0; > r=
aise notice 'select * from ptest1 order by $1 asc'; I don't think pl=
pgsql expands variables within strings. You'll have to concatenate yours=
elf, like so:
    raise notice 'select * from ptest1 order by ' || $1 ||=
' asc'; HTH -- tomas -----BEGIN PGP SIGNATURE-----<br=

Quote:

Version: GnuPG v1.2.5 (GNU/Linux) iD8DBQFDO4EaBcgs9XrR2kYRAsy5AJ9J4=
a1cwY10mVriwvWVY/zdL30CKwCfWdgw

rU3My3azyCCT8AG5iMIGXpk=3D =3D/8Tn -----END PGP SIGNATURE-----<br=

Quote:

br> </blockquote></div><br

------=_Part_4887_1726058.1128038942730--

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

[BUGS] Column Name parameter problem

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

[BUGS] Column Name parameter problem - 09-28-2005 , 02:27 PM

Re: [BUGS] Column Name parameter problem - 09-28-2005 , 02:56 PM

Re: [BUGS] Column Name parameter problem - 09-29-2005 , 12:57 AM

Re: [BUGS] Column Name parameter problem - 09-29-2005 , 07:09 PM