Re: pl/sql resources for pl/pgsql?

Hi

it is funny, so I found similar rules for developing plpgsql :)

>
> *) 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.
>

I am planning integrate main SQL parser for integrated SQL statement
in plpgsql code. There is only one, but significant issue - doubled
query parsing. So first function call should be little bit slower than
now. But it allows to control priority between plpgsql and sql
identifiers, and it allows show warnings when identifiers are
ambigonuous.

> *) 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').
>

it's valid for DDL statements too - well known case with identifiers
inside double apostrophes - lot of CASE tools produce it.

> *) misc:
>  *) never declare a function to return void

??? why - when we have not procedures ?

>  *) 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.
>

some years ago I wrote plpgsql tutorial
http://www.postgres.cz/index.php/PL/pgSQL_%28en%29

and there are some rules too
http://www.postgres.cz/index.php/PL/pgSQL_%28en%29#Recommendation_for_design_of_saved_procedures_in_PL.2FpqSQL_language

regards
Pavel Stehule

> merlin
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>