Re: Migrate Store Procedure Sybase to PostgreSQL

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