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 <[EMAIL PROTECTED]> 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-----
