From: | Filip Rembiałkowski <plk(dot)zuber(at)gmail(dot)com> |
---|---|
To: | David Waddy <david(at)littleriver(dot)ca> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Creating a view: ERROR: rules on SELECT must have action INSTEAD SELECT |
Date: | 2010-03-23 11:38:44 |
Message-ID: | 92869e661003230438y4f88052dh62ff1f443a36e2a0@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
David,
You cannot mix CREATE TEMP TABLE and SELECT in one CREATE VIEW statement
I really recommend looking at WITH statement
http://www.postgresql.org/docs/8.4/static/queries-with.html
Adjusting to your example:
CREATE VIEW test_view AS WITH q AS ( SELECT 2 as two, 3 as three )
SELECT *, two+three as five FROM q;
2010/3/19 David Waddy <david(at)littleriver(dot)ca>:
> I get the following error when trying to create a view with the
> following trivial example.
>
> SQL error:
> ERROR: rules on SELECT must have action INSTEAD SELECT
>
> In statement:
> CREATE VIEW "nutrition"."test_view" AS SELECT 2 as two, 3 as three
> INTO TEMP temp_table; SELECT *,two+three as five FROM temp_table;
>
> If I rewrite the statement to maybe make more sense:
>
> SQL error:
>
> ERROR: temporary tables cannot specify a schema name
>
> In statement:
>
> SELECT 2 as two, 3 as three INTO TEMP nutrition.temp_table; CREATE
> VIEW "nutrition.test_view" AS SELECT *,two+three as five FROM
> nutrition.temp_table;
>
> So the question is how do I use the powerful feature of temporary
> tables with views? And if this is not possible, how do I do something
> similar for optimization reasons (ie pretend "two+three as five" is
> some very expensive operation and "two" and "three" were also
> expensive operations)?
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Filip Rembiałkowski
JID,mailto:filip(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/
From | Date | Subject | |
---|---|---|---|
Next Message | Filip Rembiałkowski | 2010-03-23 11:48:25 | Re: strange |
Previous Message | Dimitri Fontaine | 2010-03-23 11:08:48 | Re: pgreplay log file replayer released |