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

On Sun, Dec 28, 2003 at 12:57:10PM -0500, Casey Allen Shobe wrote:
> 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).

Ah, I see what you mean. The psql ones can be used in queries, as long as
it's not inside a string (eg function body IIRC).

kleptog=# \set var 31
kleptog=# select :var;
?column?
----------
31
(1 row)

<examples>
> http://199.72.170.146/~sigthor/documents/example_query.txt
> http://199.72.170.146/~sigthor/documents/example_query2.txt

Aah, right. In those situations I tend to use temp tables myself. For
example, I have some programs which run a bit like:

select into temp month month from <rest of SQL statement>

select <really complicated SQL that references month.month>

Unfortunatly recent versions of Postgres tend to complain about missing
tables in FROM clause which is mildly irritating, since they're not really
tables from my point of view. Also, sometimes you need to run a quick
analyze over the table to give the planner the right hints.

Not ideal I'll grant you. In some ways some syntactic sugar would be nice.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> (... have gone from d-i being barely usable even by its developers
> anywhere, to being about 20% done. Sweet. And the last 80% usually takes
> 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce