Thread: function that returns a set of records and integer(both of them)
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, i'm migrating to postgresql and i have stored procedures like this in my sql server database, can i do that with postgresql?
Thanks.........
Get news, entertainment and everything you care about at Live.com. Check it out!
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, i'm migrating to postgresql and i have stored procedures like this in my sql server database, can i do that with postgresql?
Thanks.........
Get news, entertainment and everything you care about at Live.com. Check it out!
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 return 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!
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
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!
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
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!
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!
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!
> 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!
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!