Re: [SQL] function that returns a set of records and integer(both of them)‏

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!

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql