Thread: pl/sql resources for pl/pgsql?

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
thinon the ground, to use books on pl/sql for developing pl/pgsql code? 

gvim

Hello

documentation is very good
http://www.postgresql.org/docs/8.3/static/plpgsql.html

and some articles: http://www.postgres.cz/index.php/PL/pgSQL_%28en%29

regards
Pavel Stehule

2009/6/16 gvimrc <gvimrc@googlemail.com>:
> 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?
>
> gvim
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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.

bye.

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

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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Tuesday 16 June 2009 03:16:19 gvimrc 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?

Perhaps you will find <http://www.slideshare.net/petereisentraut/porting-
oracle-applications-to-postgresql> useful, especially page 44 and following.

On Sat, Jun 20, 2009 at 4:01 PM, Pavel Stehule<pavel.stehule@gmail.com> wrote:
>> *) misc:
>>  *) never declare a function to return void
>
> ??? why - when we have not procedures ?

The main reason is that functions returning void can not be used with
binary protocol.

merlin