Re: pl/sql resources for pl/pgsql?

On Sat, Jun 20, 2009 at 1:54 AM, Gurjeet Singh<singh.gurjeet@gmail.com> wrote:
> On Sat, Jun 20, 2009 at 10:52 AM, Jasen Betts <jasen@xnet.co.nz> wrote:
>>
>> On 2009-06-16, gvimrc <gvimrc@googlemail.com> wrote:
>> > I'm fairly new to PostgreSQL and completely new to using pl/pgsql
>> > though I've used MySQL's procedural language a little.
>> > I heard pl/pgsql is similar to Oracle's pl/sql so would it be possible,
>> > given that pl/pgsql literature is a bit thin on the ground, to use books
>> > on pl/sql for developing pl/pgsql code?
>>
>> For inspiration perhaps, not as a textbook.
>> differences from oracle:
>> http://www.postgresql.org/docs/8.3/interactive/plpgsql-porting.html
>>
>> the postgresql manual chapter on pl-pgsql (and the preceeding chapters)
>> contain all that you really need.
>>
>> pl-pgsql chapter:
>> http://www.postgresql.org/docs/8.3/interactive/plpgsql.html
>> full manual:
>> http://www.postgresql.org/docs/8.3/interactive/index.html
>>
>> plpgsql is much like any other procedural language
>> only you can embed SQL queries very very easily
>>
>> some hints you may find useful:
>>
>> The syntax checker doesn't always give useful advice when it rejects
>> your code so develop a habit of step-wise testing.
>>
>> if you say "IF" and forget to say "THEN" it will lead a confusing
>> error message.
>>
>> "ELSIF" (and "ELSEIF") are different to "ELSE IF" in the number of
>> "END IF"s youll need to use later.
>>
>> -- sql comments and
>> /* c-style
>> comments */ can both be used.
>>
>
> And a major one is, that it cannot detect semantic error's (like missing
> table, or wrong column name, or wrong expression assignment) until you
> execute the function. This implies that if you have branches in code, say IF
> .. THEN .. ELSE .. END IF then you will not see errors from a branch until
> that branch of code is executed.

Well, I happen to think that the pl/pgsql documentation is pretty
good, in that it describes the capability of the language and its
general use.  That said, the documentation gives you the 'what', but
not the 'why' or the 'when'.  In particular:

*) style:
I suggest prefixing all local variables, including inputs to and
outputs from functions.  I prefix my variables with an underscore.  If
you neglect to do this you will end up having name clashes with your
table columns...this can cause errors or unexpected effects like
turning an inner join into an outer join :-).  I would using
indentation and case rules similar to C.  Personally, I don't upper
case all SQL keywords...I think that's lame and a bit too much like
cobol.
Keep your procedures short.  Don't use loops when a query is more appropriate.

*) management:
Treat all your procedures like source code: maintain them in files
outside the database and use psql or a make system to load them into
the database.  Check your files into source control as you would any
other code.  I would specifically advise not to use a tool like
pgadmin to maintain your procedure code in a general way...learn to
write everything yourself (including the 'create function statement').

*) misc:
  *) never declare a function to return void
  *) use 'returns query' when possible
  *) parameterize your execute statements where possible
  *) generally prefer arrays of composites to temp tables where
possible (think of composite array to const temp table)
  *) if arrays dont work, prefer refcursors to temp tables
  *) don't overuse subtransactions (begin->exception->end;)

Above all, don't use function where a view or a query is more appropriate.

merlin