Thread: functions

Hi, there!
I am new to postgres and I have problems with some
things that are very simple in MS-SQL.

I understand that the PG synonym of MS-SQL stored
procedure is the 'function'. Am I right?

If so, then is it possible to return a multi-field,
multi-row set from a function (just the way a simple
'select' does in MS-SQL stored procs)?


Thanks.
Andrei


__________________________________________________
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com

Andrei,

> I understand that the PG synonym of MS-SQL stored
> procedure is the 'function'. Am I right?
>
> If so, then is it possible to return a multi-field,
> multi-row set from a function (just the way a simple
> 'select' does in MS-SQL stored procs)?

There is an awkward way of doing this in Postgres 7.2.1, involving CURSORS.
In 7.3, which will be out in beta sometime soon, it will be possible to have
T-SQL-like row-returning Functions.

There is one other difference btw. Postgres Functions and MSSQL Procedures;
Postgres functions may be called within a query, and MSSQL procdedures may
not.  On the other hand, MSSQL procedures may return multiple result sets,
and I don't think anyone is proposing this for Postgres.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco

Thanks, Josh.

I really do not understand the philosophy involved.
Usually the client needs very often this kind of sets.
So, the only way for a client to get them is to issue
hand-made query strings
(e.g. pg_query(cnn, 'SELECT * FROM ...'), in PHP)?
Where are the advantages of pre-compiled code,
execution plans, etc? Probably it will take me some
time to accommodate.

And by the way, MSSQL has also functions. I think they
satisfy your requirements :)


Thanks.
Andrei





Josh Berkus wrote:Andrei,> I understand that the PG
synonym of MS-SQL stored> procedure is the 'function'.
Am I right?> > If so, then is it possible to return a
multi-field,> multi-row set from a function (just the
way a simple> 'select' does in MS-SQL stored
procs)?There is an awkward way of doing this in
Postgres 7.2.1, involving CURSORS. In 7.3, which will
be out in beta sometime soon, it will be possible to
have T-SQL-like row-returning Functions.There is one
other difference btw. Postgres Functions and MSSQL
Procedures; Postgres functions may be called within a
query, and MSSQL procdedures may not. On the other
hand, MSSQL procedures may return multiple result
sets, and I don't think anyone is proposing this for
Postgres.-- -Josh BerkusAglio Database SolutionsSan
Francisco---------------------------(end of
broadcast)---------------------------TIP 3: if
posting/reading through Usenet, please send an
appropriatesubscribe-nomail command to
majordomo@postgresql.org so that yourmessage can get
through to the mailing list cleanly

__________________________________________________
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com

Andrei,

> I really do not understand the philosophy involved.
> Usually the client needs very often this kind of sets.
> So, the only way for a client to get them is to issue
> hand-made query strings
> (e.g. pg_query(cnn, 'SELECT * FROM ...'), in PHP)?
> Where are the advantages of pre-compiled code,
> execution plans, etc? Probably it will take me some
> time to accommodate.

Nothing to understand.  The Postgres team just didn't have the code for
rowset-returning functions until recently.   We always wanted them.

> And by the way, MSSQL has also functions. I think they
> satisfy your requirements :)

Yeah.  I haven't really used MSSQL since 7.0; I've heard that 2000 has custom
functions, but I haven't tested them.

--
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                        Josh Berkus
   Complete information technology     josh@agliodbs.com
    and data management solutions     (415) 565-7293
   for law firms, small businesses      fax 621-2533
    and non-profit organizations.     San Francisco