Fabien, I don't really see the point of "persistent variables". What benefit do they add over relations?
You can add a simple function to fetch a tuple if you want it not to look like a subquery. Do it with heap access in C if you like, save the (trivial) planning costs.
I do see value to two different things discussed here:
* Pavel's proposal for persistent-declaration, non-persistent-value session variables with access control. These will be very beneficial with RLS, where we need very fast lookups. Their purpose is that you can set up expensive state with SECURITY DEFINER functions, C-level functions, etc, then test it very cheaply later from RLS and from other functions. Their advantage over relations is very cheap, fast access.
I can maybe see global temporary relations being an alternative to these, if we optimise by using a tuplestore to back them and only switch to a relfilenode if the relation grows. The pg_catalog entries would be persistent so you could GRANT or REVOKE access to them, etc. Especially if we optimised the 1-row case this could work. It'd be less like what Oracle does, but might let us re-use more functionality and have fewer overlapping features. Pavel?
This is hard question - I have different expectation from variables and from tables. Sure, there can be a optimization for one row global temporary tables, but it fails on first update. And with this optimization we should not to use current heap access functionality - or we increase a complexity of currently lot of complex code. From success global temp tables implementation I am expecting unbloating catalogue and all relation related functionality - indexes, column statistics, usage statistics, MVCC. I can't to imagine a one row optimization there. More it has limit - it is pretty hard implement there expandable types.
I see very useful to define variable as memory based NOT ACID, not MVCC storage (always). Tables are ACI(D) MVCC. The minimalism is great, but have to has practical limit - we have variables inside/outside plpgsql - although we can use temporary tables.
There are secondary question if we can introduce some better interface for writing getter/setter function, where current relations can be used.
* Fabien's earlier mention of transient session / query variables, a-la MySQL or MS-SQL. They're obviously handy for more general purpose programming, but our strict division between SQL and plpgsql makes them a bit less useful than in MS-SQL with T-SQL. I think it's a very separate topic to this and should be dealt with in a separate thread if/when someone wants to work on them.