Re: Stored procedures returning rowsets

Jaroslaw Nozderko sez:
} Hi Mark,
}
} >
} > Previously
} >
} > >Perhaps not all these factors are always important, but in big and
} > >heavy loaded systems it's really unimaginable to send "raw" queries.
} >
} > Interesting,
} >
} > I have heard this often... mainly from SQL Server folk....
} > I find it hard to understand what is bad about "raw" sql....
} > here is why :
} >
} > As I understand it, every query goes through the stages of
} > parse,execute, [and possibly fetch(es)].
}
} I think there is also plan/optimize stage between parse and execute...
} And it may be expensive. Another advantage of stored procedures
} is possibility of encapsulating business logic on server side only.

Excapsulating business logic on the DB server seems to be the best reason
I've heard. In fact, I am in the process of writing a large web application
and, more and more, I find that I want to write server-side plpgsql
functions to encapsulate transactions, simplifying the programmatic
interaction with the database to SELECT Func(args, ...) for the most part,
particularly for updates.

It is not, however, clear to me the difference between a stored procedure
which can be CALL'd and a function which must be SELECT'd. Can anyone
explain why the distinction is important?

} Regards,
} Jarek
--Greg