Waiting for PostgreSQL 17 – In plpgsql, allow %TYPE and %ROWTYPE to be followed by array decoration.

On 4th of January 2024, Tom Lane committed patch:

In plpgsql, allow %TYPE and %ROWTYPE to be followed by array decoration.
 
This provides the useful ability to declare a variable that is an array
of the type of some other variable or some table column.
 
Quan Zongliang, Pavel Stehule
 
Discussion: https://postgr.es/m/ec4523e1-9e7e-f3ef-f9ce-bafd680ad6f6@yeah.net

Continue reading Waiting for PostgreSQL 17 – In plpgsql, allow %TYPE and %ROWTYPE to be followed by array decoration.

Getting value from dynamic column in pl/PgSQL triggers?

Every so often, on irc, someone asks how to get value from column that is passed as argument.

This is generally seen as not possible, as pl/PgSQL doesn't have support for dynamic column names.

We can work around it, though. Are the workarounds usable, in terms of performance?

Continue reading Getting value from dynamic column in pl/PgSQL triggers?

Foreign Key to partitioned table – part 2

Previously I wrote about how to create foreign key pointing to partitioned table.

Final solution in there required four separate functions and four triggers for each key between two tables.

Let's see how fast it is, and if it's possible to make it simpler.

Continue reading Foreign Key to partitioned table – part 2

Variables in SQL, what, how, when?

One of the questions that gets asked quite a lot, is: how can you use variables in your queries? Other databases have them, does PostgreSQL?

Actually the answer is “no". But, it is actually very easy to work around, and what's more important – quite often, what you need are not variables in queries, but rather variables in client. What does it mean? Let's see.

Continue reading Variables in SQL, what, how, when?

Waiting for 9.2 – NULLS from pg_*_size() functions

On 19t of January, Heikki Linnakangas committed patch:

Make pg_relation_size() and friends return NULL if the object doesn't exist.
 
That avoids errors when the functions are used in queries like "SELECT
pg_relation_size(oid) FROM pg_class", and a table is dropped concurrently.
 
Phil Sorber

Continue reading Waiting for 9.2 – NULLS from pg_*_size() functions

Waiting for 9.2 – Stacked Diagnostics in PL/pgSQL

On 18th of July, Tom Lane committed patch:

Add GET STACKED DIAGNOSTICS plpgsql command to retrieve exception info.
 
This is more SQL-spec-compliant, more easily extensible, and better
performing than the old method of inventing special variables.
 
Pavel Stehule, reviewed by Shigeru Hanada and David Wheeler

Continue reading Waiting for 9.2 – Stacked Diagnostics in PL/pgSQL

Waiting for 9.1 – FOREACH IN ARRAY

On 16th of February, Tom Lane committed patch:

Add FOREACH IN ARRAY looping to plpgsql.
 
(I'm not entirely sure that we've finished bikeshedding the syntax details,
but the functionality seems OK.)
 
Pavel Stehule, reviewed by Stephen Frost and Tom Lane

Continue reading Waiting for 9.1 – FOREACH IN ARRAY

How to manage changes to your database?

Every now and then somebody asks how to make diff of database schemata.

Usual background is like: we have production database, and development database, and we want to see what is different on development to be able to change production in the same way.

Personally I think that such approach is inherently flawed. Why?

Continue reading How to manage changes to your database?