Thread: Re: Migrate Store Procedure Sybase to PostgreSQL
In article <23aa5c00.0112190638.2f919e52@posting.google.com>, Yamil Bendek wrote: > yibendek@ubiquando.com.co (Yamil Bendek) wrote in message news:<23aa5c00.0112181417.5265270a@posting.google.com>... > > I need to migrate a Sybase Database to PostgreSQL, In the Sybase DB we > have store procedure that return result Sets, how can i to migrate > these store procedure????????? you could try storing the result in a tmp table and returning the name of the table. I dont think you can return a result set from a stored procedure. good luck marc > > >> is it possible to create or to write store procedures that return a >> Result Set of a Query? How, Please ? >> >> Similar to the store procedures of sybase >> >> Example sybase : >> >> create procedure sp_sv44InfCiudades @s_cod_pais smallint as >> SELECT ciudades.cciu_codigo_iata, >> ciudades.vciu_nombre_ciudad, >> paises.vpai_nombre_pais >> FROM ciudades, >> paises >> WHERE ( paises.spai_codigo_pais = ciudades.sciu_codigo_pais ) and >> ( paises.spai_codigo_pais = @s_cod_pais ) >> ORDER BY ciudades.vciu_nombre_ciudad ASC >> >> >> >> >> >> return multiple rows and columns >> >> that is possible in postgeSQL ?
Marc Spitzer wrote: > Yamil Bendek wrote: >> I need to migrate a Sybase Database to PostgreSQL, In the Sybase DB we >> have store procedure that return result Sets, how can i to migrate >> these store procedure????????? > > you could try storing the result in a tmp table and returning the > name of the table. I dont think you can return a result set from > a stored procedure. Somewhere in documentation I found that it could return one column from SQL function, but that's all it can do. What a shame. Anyway, it's a design flaw that needed to be fixed a long time ago. I hope it it will be done in v7.2 (it would be nice to hear some developer here too). -- Marius Žalinauskas
There are various workarounds, depending on what you
are trying to achieve. Does the following look
promising to you?
jeff=# \d test
Table "test"
Attribute | Type | Modifier
-----------+---------+-------------------------------------------------
id | integer | not null default
nextval('"test_id_seq"'::text)
stuff | text |
Index: test_id_key
jeff=# select * from test;
id | stuff
----+-----------
1 | this
2 | that
3 | the_other
(3 rows)
jeff=# \! more test_func
drop function test();
create function test() returns text as '
declare
output text := '''';
each_line test%ROWTYPE;
begin
for each_line in select * from test loop
output := output || cast (each_line.id as text) ||
chr(9) || each_line.stuff || chr(10);
end loop;
return output;
end;
' language 'plpgsql';
jeff=# \a
Output format is unaligned.
jeff=# select test();
test
1 this
2 that
3 the_other
(1 row)
jeff=# \q
jeff@akira=> psql -A -c "select test()" -d jeff
test
1 this
2 that
3 the_other
(1 row)
--- Marius �alinauskas <mariusz@delfi.lt> wrote:
> Marc Spitzer wrote:
> > Yamil Bendek wrote:
> >> I need to migrate a Sybase Database to
> PostgreSQL, In the Sybase DB we
> >> have store procedure that return result Sets, how
> can i to migrate
> >> these store procedure?????????
> >
> > you could try storing the result in a tmp table
> and returning the
> > name of the table. I dont think you can return a
> result set from
> > a stored procedure.
>
> Somewhere in documentation I found that it could
> return one column from SQL
> function, but that's all it can do. What a shame.
>
> Anyway, it's a design flaw that needed to be fixed a
> long time ago. I hope
> it it will be done in v7.2 (it would be nice to hear
> some developer here
> too).
>
> --
> Marius �alinauskas
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to majordomo@postgresql.org
> so that your
> message can get through to the mailing list cleanly
__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com