RE: function that returns a set of records and integer(both of them)‏

From: daniel blanco <daniel_b_adrian(at)hotmail(dot)com>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: RE: function that returns a set of records and integer(both of them)‏
Date: 2008-07-13 06:53:34
Message-ID: BLU143-W222B81096CEEA046864C4B7920@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(dot)stehule(at)gmail(dot)com> To: daniel_b_adrian(at)hotmail(dot)com> Subject: Re: [SQL] function that returns a set of records and integer(both of them)‏> CC: pgsql-sql(at)postgresql(dot)org> > Hello> > 2008/7/13 daniel blanco <daniel_b_adrian(at)hotmail(dot)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 t
o 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(dot)stehule(at)gmail(dot)com> >> To: daniel_b_adrian(at)hotmail(dot)com> >> Subject: Re: [SQL] function that returns a
set of records and integer(both> >> of them)‏> >> CC: pgsql-sql(at)postgresql(dot)org> >>> >> 2008/7/11 daniel blanco <daniel_b_adrian(at)hotmail(dot)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 workaro
und, 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(at)postgresql(dot)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!
http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Pavel Stehule 2008-07-13 10:40:29 Re: function that returns a set of records and integer(both of them)‏
Previous Message Pavel Stehule 2008-07-13 05:06:07 Re: function that returns a set of records and integer(both of them)‏