SQL久しぶりすぎて色々と忘れていた。

自己結合っぽいのどうやってやるんだっけと悩んで小一時間（アホすぎ
軽くメモっておく。

SELECT snode.ipaddress AS snode, dnode.ipaddress AS dnode FROM traffic, node AS snode, node AS dnode WHERE traffic.source = snode.nodeid AND traffic.destination = dnode.nodeid;

trafficipとかいうviewを作る事を考えるとこんな感じだろうか？

CREATE VIEW trafficip SELECT fileid, line, dtime, snode.ipaddress AS snode, sport, dnode.ipaddress AS dnode, dport, mes, errorcheck FROM traffic, node AS snode, node AS dnode WHERE traffic.source = snode.nodeid AND traffic.destination = dnode.nodeid;

とか思って作成したら、以下のように怒られた。

=# CREATE VIEW trafficip SELECT fileid, line, dtime, snode.ipaddress AS snode, sport, dnode.ipaddress AS dnode, dport, mes, errorcheck FROM traffic, node AS snode, node AS dnode WHERE traffic.source = snode.nodeid AND traffic.destination = dnode.nodeid;
ERROR: syntax error at or near "SELECT"
LINE 1: CREATE VIEW trafficip SELECT f

PostgreSQLの場合はCREATE VIEW [TABLE_NAME] AS SELECT ...としなきゃならんらしい。
ので、

CREATE VIEW trafficip AS SELECT fileid, line, dtime, snode.ipaddress AS snode, sport, dnode.ipaddress AS dnode, dport, mes, errorcheck FROM traffic, node AS snode, node AS dnode WHERE traffic.source = snode.nodeid AND traffic.destination = dnode.nodeid;

としたら上手く動作してくれた。