Re: My first PL/pgSQL function

> ​For posterity here is the final version. I ran it through PostgreSQL
> 9.5beta1 this morning so it's at least syntactically valid. Additionally I
> went w/ a list of INTO targets instead of a RECORD because it's a more
> elegant solution in that it made the code a little less verbose and a
> little less repetitive. The fact that in some cases it's faster is a
> serendipitous bonus.
>
> Though the conversation around this function has improved my understanding
> of PL/pgSQL immensely there are a couple things that happened that I don't
> fully understand:
>
> 1. I've changed the function's argument list from: (text, variadic
> numeric[]) to: (text, int default, numeric default) because I couldn't
> get the variadic version to work when only one argument was passed to the
> function. For example:
> SELECT * FROM check_discount_code('blah')
> caused PostreSQL to complained that "no function w/ that signature exists
> you may need to cast" (I'm paraphrasing). In order to get it to work I had
> to provide at least two arguments.
>

yes - the variadic *functions* can have parameters with default values or
*variadic parameters* or mix. It is similar to Visual Basic for example.
The specific PostgreSQL feature is support function overloading and
variadic functions together. When you call any function, then PostgreSQL
have to choose function with the closest param types - and usually Postgres
try to cast parameters to different type less times than other databases.

>
> 2. I was under the impression that the runtime environment of PL/pgSQL is
> the same environment PostgreSQL uses to execute all SQL commands and
> functions. So if that's true why is returning JSON from inside a PL/pgSQL
> function so much more expensive than doing it outside?
>

JSON in Postgres is string. If you need a access to any field, then this
string have to be parsed and the data must be deserialized. The same behave
has XML format. If I don't plan to process result of function on server
side, then returning XML or JSON is ok. But If I'll process result in other
server side functions, then native tuples are better (if nested structures
are not needed.)

Regards

Pavel

>
> Dane
> ​
> ​
>
>