Re: "CREATE TABLE table_name AS EXECUTE name WITH DATA" becomes syntax error. - Mailing list pgsql-general

On Thursday, November 24, 2011 1:55:53 am Naoya Anzai wrote:
> Hi,
>
> Accroding to
> http://www.postgresql.org/docs/9.1/interactive/sql-createtableas.html ,
> "CREATE TABLE table_name AS EXECUTE name WITH DATA" seems a right syntax,
> but,this statement becomes a SYNTAX ERROR.
> Is this a specification?
> ---
> naoya=# SELECT VERSION();
>                                                     version
> ---------------------------------------------------------------------------
> ------------------------------------ PostgreSQL 9.1.1 on
> x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat
> 4.1.2-48), 64-bit (1 row)
>
> naoya=# PREPARE GETONE AS SELECT * FROM SEED WHERE ID=1;
> PREPARE
>
> naoya=# EXECUTE GETONE;
>  id |            date
> ----+----------------------------
>   1 | 2011-11-24 11:24:49.675427
> (1 row)
>
> naoya=# CREATE TABLE NEW_SEED AS EXECUTE GETONE;
> SELECT 1
>
> naoya=# CREATE TABLE NEW_SEED2 AS EXECUTE GETONE WITH DATA;
> ERROR:  syntax error at or near "WITH DATA" at character 42
> STATEMENT:  CREATE TABLE NEW_SEED2 AS EXECUTE GETONE WITH DATA;


Order of execution?

Example from link above:
PREPARE recentfilms(date) AS
  SELECT * FROM films WHERE date_prod > $1;
CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS
  EXECUTE recentfilms('2002-01-01');

So for your case:
CREATE TABLE NEW_SEED2  WITH DATA AS EXECUTE GETONE;

> ---
>
> Regards.
>
>
> ---
> Naoya Anzai

--
Adrian Klaver
adrian.klaver@gmail.com