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

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 (sessi on 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 statemen t: 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.
> >>
> >> Re gards
> >> 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!