Saturday, August 18

Tag: cursors

PG Phriday: Uptown Func

Shaun's PlanetPostgreSQL
One of the coolest things about Postgres functions is that they can return rows as if they were a table. Not only is it possible, but creating a set-returning function in Postgres is almost frighteningly trivial when compared to other database engines. In fact, some Oracle users are probably rolling their eyes and muttering "Just use a cursor!" already. Just hear us out! Postgres functions can return cursors too, but using them afterwards isn't exactly a friendly experience. Cursors are passed by reference, so the function must either accept a parameter to name the cursor, or it generates something wacky like "<unnamed portal 1>". After that, cursors can only be used with FETCH instead of SELECT, greatly limiting their utility. What about views, then? Obviously they can return (more…)