Variables in PostgreSQL? [was: Is my MySQL Gaining?]

Martijn van Oosterhout (Sunday 28 December 2003 04:56)
> Interesting, I found them in psql's manpage under ADVANCED FEATURES -
> VARIABLES. Let's see if I can find it on the web... Here's a web version of
> the manpage.

Ahh, I have seen those...but they're specific to psql, and if memory serves me
correct I wasn't able to use the variables within queries, either.  I need
something I can use over ODBC (within a single transaction, of course).
These can sometimes solve problems that you can't seem to solve any other
way, and other times can improve query response time *greatly* (say, by
running a subquery once and assigning the result to a variable used 40 times
in the final statement instead of running 40 subqueries).

Take, for example, these query which I wrote in Transact-SQL for Microsoft
SQL.  Yes, this was a horribly-formed database and the requests complex, but
it's something I had to deal with on a daily basis when I was still employed.

This example shows a scenario where I don't think I could even write the query
without the use of SQL variables:
http://199.72.170.146/~sigthor/documents/example_query.txt

This example shows a scenario where the variables are re-used.  In this
example, changing the original query to use variables instead reduced query
execution time from 40 seconds to 2:
http://199.72.170.146/~sigthor/documents/example_query2.txt

(note for clarity that wherever [[blah]] appears in the SQL, this was replaced
by an actual value with PHP before execution)

So I guess my real question is, how can I address the same issues in
PostgreSQL?

Vertu sæll,

--
Sigþór Björn Jarðarson (Casey Allen Shobe)
http://rivyn.livejournal.com