AT TIME ZONE INTERVAL and prepared statements
AT TIME ZONE INTERVAL $1 doesn't seem to be supported in prepared
statements.
This works:
SELECT now() AT TIME ZONE INTERVAL '-08:00';
This doesn't:
PREPARE mys(text) AS SELECT now() AT TIME ZONE INTERVAL $1;
ERROR: syntax error at or near "$1" at character 57
These work:
PREPARE mys(interval) AS SELECT now() AT TIME ZONE $1;
PREPARE mys(text) AS SELECT now() AT TIME ZONE ($1::interval)
Any reason why the second case doesn't work?
Kris Jurka
Kris Jurka <books@ejurka.com> writes:
AT TIME ZONE INTERVAL $1 doesn't seem to be supported in prepared
statements.
Try
AT TIME ZONE $1
or possibly
AT TIME ZONE $1::interval
You are confusing the syntax for a typed literal with part of the AT
TIME ZONE construct.
regards, tom lane
On Fri, 22 Oct 2004, Tom Lane wrote:
You are confusing the syntax for a typed literal with part of the AT
TIME ZONE construct.
Could you explain what the difference is between a typed literal
"int '11'" and a cast(ed) literal "'11'::int" to me they seem like the
same thing.
Kris Jurka
Kris Jurka <books@ejurka.com> writes:
On Fri, 22 Oct 2004, Tom Lane wrote:
You are confusing the syntax for a typed literal with part of the AT
TIME ZONE construct.
Could you explain what the difference is between a typed literal
"int '11'" and a cast(ed) literal "'11'::int" to me they seem like the
same thing.
Yes, they are semantically the same. The first one is a kluge that Tom
Lockhart put in to approximate the SQL spec's demands for representation
of timestamp and interval constants. It does not work syntactically to
try to extend it to a general cast construct (we can only barely manage
to support it as-is --- there are various special cases that don't work
such as arrays and qualified type names, and a depressingly large part
of gram.y is devoted to getting the cases that do work to work). Even
if we could get the parser to cope with that without reduce/reduce
errors, we'd be greatly reducing the grammar's ability to detect actual
syntax errors, because practically anything next to practically anything
else would become a potentially legal cast construct.
So, no "INTERVAL $1". Bad enough we have to take "INTERVAL 'string'".
This wasn't one of the SQL committee's better ideas.
regards, tom lane