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
