Re: [SQL] date question

[javier garcia - CEBAS]

Hi all;

Peter, thank you very much for your help. Just a little thing. I've done as 
you say:

CREATE TABLE rain_series_dated AS SELECT (year * interval '1 year' + month * 
interval '1 month' + day * interval '1 day') AS 
fecha,est7237,est7238,est7239,est7250 FROM rain_series ORDER by fecha;

I see that the obtained new field is "interval" type:

 Column  |   Type   | Modifiers
-+--+---
 fecha   | interval |
 est7237 | real |
 est7238 | real |


And I've tried to use this result to be compared with my other table in which 
"fecha" is "date" type. The result is that the query halts with no result; I 
guess that it is because it tries to compare different data types.

Is it possible to cast the "interval" obtained type into a "date" one in the 
creation of the mentioned table?  (I've looked it in the documentation, but I 
can't find the answer)

Thanks and regards,
Javier

El Mar 17 Jun 2003 20:09, escribió:
> javier garcia - CEBAS writes:
> > Is there a way to create a table from this table, directly in Postgres in
> > which a date field is created based in the values of "year", "month",
> > "day" in this table?
>--
> year * interval '1 year' + month * interval '1 month' + day * interval '1
> day'
>
> This results in a timestamp value that you can compare to or assign to a
> date value.

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[SQL] plpgsql, double quoted column names containing spaces/hyphens

[Knut P. Lehre]

Using pgsql 7.2.1 (PeerDirect beta4) on WinXP Prof SP1. Client: MS 
VStudio2003 C++ -> ADO -> ODBC 7.03.01.00.

In plpgsql, I can access columns with a name containing capital letters by 
enclosing the name in double quotes: tbl."ColName"
However, if the quoted name contains spaces or hyphens I get an error 
message. It is surprising that this does not work in plpgsql when it does 
with sql.

Thus, the following line produces an error (unterminated ") in plpgsql:
IF OLD."-person-initials" ... THEN
However, I discovered that rewriting using EXECUTE does work:
DECLARE curs1 refcursor; personinitials int4;
...
OPEN curs1 FOR EXECUTE ''SELECT "-person-initials" AS pers FROM 
"''||TG_RELNAME||''" WHERE id=''||OLD.id;
FETCH curs1 INTO personinitials;
CLOSE curs1;
IF personinitials ... THEN

Could I have solved this problem in an easier way, by using some kind of 
escape codes in the example producing the error (IF OLD."-person-initials" 
... THEN)?

Thanks, KP

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [SQL] plpgsql, double quoted column names containing spaces/hyphens

[Tom Lane]

"Knut P. Lehre" <[EMAIL PROTECTED]> writes:
> Using pgsql 7.2.1 (PeerDirect beta4) on WinXP Prof SP1. Client: MS 

> In plpgsql, I can access columns with a name containing capital letters by 
> enclosing the name in double quotes: tbl."ColName"
> However, if the quoted name contains spaces or hyphens I get an error 
> message.

This is fixed in 7.3.

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend