Re: function that returns a set of records and integer(both of them)‏ - Mailing list pgsql-sql

Hello

8.4 can inline SRF SQL immutable functions. so

SELECT * FROM fce(param) should be efective like SELECT * from
wrapped_tab where col = param

Regards
Pavel Stehule

2008/7/13 daniel blanco <daniel_b_adrian@hotmail.com>:
> Ok, i see........... well..... when i have to return a set of records i'll
> use sql and when i have to do stored procedure of functions i'll use plpsql
> in this case but you're telling me that it will change in the 8.4 version.
> won't it?
>
>
>
>
>> Date: Sun, 13 Jul 2008 07:06:07 +0200
>> From: pavel.stehule@gmail.com
>> To: daniel_b_adrian@hotmail.com
>> Subject: Re: [SQL] function that returns a set of records and integer(both
>> of them)‏
>> CC: pgsql-sql@postgresql.org
>>
>> Hello
>>
>> 2008/7/13 daniel blanco <daniel_b_adrian@hotmail.com>:
>> > Ok, thanks pavel............. i think i'll try set of cursors..... by
>> > the
>> > way in t-sql i did it as follows:
>> > table users with a field called name
>> >
>> > create procedure list_user_by_name
>> > @info_name varchar
>> > as
>> > declare @sw
>> > begin
>> > select @n=(select count(*) from users where name like
>> > (@info_name+'%'))
>> > if @sw>0
>> > begin
>> > select * where name like (@info_name+'%')
>> > return 1
>> > end
>> > else
>> > begin
>> > return 0
>> > end
>> > end
>> >
>>
>> do you thing return_status or global variables? It doesn't exist in
>> PostreSQL. Your sample is typical example, what can be in stored
>> procedure for MS and what would not be on Oracle like rdbms. This code
>> is little bit ineffective. You have to call seq scan on users two
>> times. Minimally this code on pg and oracle is:
>>
>> create function list_user_by_name(info_name)
>> returns users as $$
>> select * from users where name like $1 || '%'
>> $$ language sql strict;
>> -- you don't need logical info about returned set - this information
>> is inside returned set, it is just empty or not empty.
>>
>> but it's better use directly well select than stored procedure in this
>> case (8.4 will be different) - On Oracle like rdbsm SELECTs are not
>> wrapped to procedures.
>>
>> p.s. postgresql doesn't support global (session variables) - this
>> topic was discussed in different thread this week
>>
>> Regards
>> Pavel
>>
>> > as you see i do a select and a return at the same time when de sw
>> > variable
>> > is > than 0. i was expecting to do a similiar function with plpgsql o
>> > sql in
>> > postgresql
>> >
>> >
>> >> Date: Sat, 12 Jul 2008 09:05:45 +0200
>> >> From: pavel.stehule@gmail.com
>> >> To: daniel_b_adrian@hotmail.com
>> >> Subject: Re: [SQL] function that returns a set of records and
>> >> integer(both
>> >> of them)‏
>> >> CC: pgsql-sql@postgresql.org
>> >>
>> >> 2008/7/11 daniel blanco <daniel_b_adrian@hotmail.com>:
>> >> > Hi Everyone
>> >> >
>> >> > I would like to know if i can create a function that returns a set of
>> >> > record
>> >> > with the sql statement: select and a integer, i mean both of them,
>> >> > because i
>> >> > remenber that in sql server (transact sql) i can do that in a stored
>> >> > procedure doing a select statement and a return of a integer in the
>> >> > same
>> >> > procedure,
>> >>
>> >> when I worked with T-SQL this wasn't possible - stored procedure
>> >> should return recordset or multirecord set or OUT variables. And it
>> >> was solution. When I would returns some similar like you, I had to
>> >> re turn two recordsets.
>> >>
>> >> i'm migrating to postgresql and i have stored procedures like
>> >> > this in my sql server database, can i do that with postgresql?
>> >>
>> >> You cannot return integer and recordset from function now. There is
>> >> one workaround, you can return set of cursors.
>> >>
>> >> Regards
>> >> Pavel Stehule
>> >>
>> >> >
>> >> > Thanks.........
>> >> > ________________________________
>> >> > Get news, entertainment and everything you care about at Live.com.
>> >> > Check
>> >> > it
>> >> > out!
>> >>
>> >> --
>> >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> >> To make changes to your subscription:
>> >> http://www.postgresql.org/mailpref/pgsql-sql
>> >
>> >
>> > ________________________________
>> > Connect to the next generation of MSN Messenger Get it now!
>
>
> ________________________________
> Invite your mail contacts to join your friends list with Windows Live
> Spaces. It's easy! Try it!