Stupid tricks – Dynamic updates of fields in NEW in PL/pgSQL

Dynamic updates of fields in NEW in PL/pgSQL

Today, on #postgresql on IRC, strk asked about updating fields in NEW record, in plpgsql, but where name of the field is in variable.

After some time, he sent his question to hackers mailing list. And he got prompt reply that it's not possible.

Well, I dare to disagree.

Continue reading Stupid tricks – Dynamic updates of fields in NEW in PL/pgSQL

Waiting for 8.5 – PL/pgSQL by default

On 18th of December Bruce Momjian committed very important, but relatively small, patch:

Log Message:
-----------
Install server-side language PL/pgSQL by default.

There is no point in showing it, commit log tells all – basically from 8.5 on PL/pgSQL will be enabled by default in all databases.

There was time when people rejected “stored procedure" suggestions, because they didn't have any PL/, and installing them required superuser privileges. For some time (since 8.3) you no longer need to be superuser to create trusted languages, so the problem mostly vanished, but now, it will simply cease to exist 🙂

Waiting for 8.5 – PL/pgSQL variable resolution

On 13th of November (I know, backlog again), Tom Lane committed patch which make PostgreSQL more strict about what happens in stored procedures in PL/pgSQL:

ADD control knobs FOR plpgsql's variable resolution behavior, and make the
default be "throw error on conflict", as per discussions.  The GUC variable
is plpgsql.variable_conflict, with values "error", "use_variable",
"use_column".  The behavior can also be specified per-function by inserting
one of
        #variable_conflict error
        #variable_conflict use_variable
        #variable_conflict use_column
at the start of the function body.
 
The 8.5 release notes will need to mention using "use_variable" to retain
backward-compatible behavior, although we should encourage people to migrate
to the much less mistake-prone "error" setting.
 
Update the plpgsql documentation to match this and other recent changes.

Continue reading Waiting for 8.5 – PL/pgSQL variable resolution

Waiting for 8.5 – Named function arguments

Pavel Stehule – hero for everybody writing stored procedures, wrote, and later Tom Lane committed patch which adds named arguments for functions:

Log Message:
-----------
Support use of function argument names to identify which actual arguments
match which function parameters.  The syntax uses AS, for example
funcname(value AS arg1, anothervalue AS arg2)
 
Pavel Stehule

Continue reading Waiting for 8.5 – Named function arguments

Waiting for 8.5 – DO

On 22nd of September, Tom Lane committed a patch by Petr Jelinek:

Log Message:
-----------
Implement the DO statement TO support execution OF PL code WITHOUT HAVING
TO CREATE a FUNCTION FOR it.
 
PROCEDURAL languages now have an additional entry point, namely a FUNCTION
TO EXECUTE an inline code block.  This seemed a better design than trying
TO hide the transient-ness OF the code FROM the PL.  AS OF this patch, ONLY
plpgsql has an inline handler, but probably people will soon WRITE handlers
FOR the other standard PLs.
 
IN passing, remove the long-dead LANCOMPILER OPTION OF CREATE LANGUAGE.
 
Petr Jelinek

Continue reading Waiting for 8.5 – DO

Calculating backlog of events to handle

Yesterday on my favorite IRC channel fooqux asked interesting question. I took some more questions, and here is problem description:

We have a system which, every 5 minutes, takes a number of tasks to be done. Tasks are uniform. Within 5 minutes we can handle at most 100 tasks. Given the history of number of tasks added every 5 minutes, calculate backlog at any given moment.

Did you understand the problem? Well – I didn't. So, let's see the data, and expected output.

Continue reading Calculating backlog of events to handle

Waiting for 8.5 – MOVE {FORWARD,BACKWARD} X

On 29th of September (I know, there is a backlog – I'll work on it, I promise), Tom Lane committed another patch from Pavel Stehule:

Allow MOVE FORWARD n, MOVE BACKWARD n, MOVE FORWARD ALL, MOVE BACKWARD ALL
in plpgsql.  Clean up a couple of corner cases in the MOVE/FETCH syntax.
 
Pavel Stehule

Continue reading Waiting for 8.5 – MOVE {FORWARD,BACKWARD} X

Getting session variables without touching postgresql.conf

This post has been updated with new code that uses temporary table – the code is at the end of post!

There was this question on Stack Overflow.

For future reference: guy asked how to do session variables – i.e. something he could define once in session, and later reuse in standard sql queries – without modifying postgresql.conf – so usage of custom_variable_classes is forbidden 🙂

While I don't actually see why somebody would want to avoid modifying its postgresql.conf (short of “it's shared hosting and I don't have superuser privileges"), I thought that it will be rather simple, and at the same time, rather interesting.

So, let's do it:

Continue reading Getting session variables without touching postgresql.conf