Re: Transition from MS-SQL - Store Procedures?

services-google(at)markpennell(dot)com (Mark E. Pennell) writes:

> I am transitioning from MS-SQL to Postgresql. In
> evaluting how to bring over MS-SQL Stored Procedures,
> I have looked at the CREATE FUNCTION capability of pg.

What you need to realize is that CREATE FUNCTION supports multiple
languages, so the answers to your questions will vary. The docs
contain (reasonably) complete descriptions of the various languages,
which include Perl, Tcl, straight SQL, and PL/pgSQL (similar to
Oracle's stored procedure language).

> I have a few questions
>
> (A) does pg support IF/THEN/ELSE logic in the
> created functions? I have not seen it anywhere.

Every language except SQL supports this, in various ways of course.

> (B) does pg support anything like @@ERROR? For instance,
> I want the next sql line in the function to be
> different depending of the result of the last.

You can definitely do the above, but if you're planning to generate
errors as a normal part of operation (I don't know what @@ERROR really
means in SQL Server) you may run into some trouble. See the section
on "Exceptions" at the bottom of:

http://www.us.postgresql.org/users-lounge/docs/7.1/postgres/plpgsql-description.html

-Doug
--
In a world of steel-eyed death, and men who are fighting to be warm,
Come in, she said, I'll give you shelter from the storm. -Dylan