On Sat, Jun 20, 2009 at 1:54 AM, Gurjeet Singh<singh.gurjeet@xxxxxxxxx> wrote: > On Sat, Jun 20, 2009 at 10:52 AM, Jasen Betts <jasen@xxxxxxxxxx> wrote: >> >> On 2009-06-16, gvimrc <gvimrc@xxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
